MySQL: View with Subquery in the FROM Clause Limitation

I had the same problem. I wanted to create a view to show information of the most recent year, from a table with records from 2009 to 2011. Here's the original query:

SELECT a.* 
FROM a 
JOIN ( 
  SELECT a.alias, MAX(a.year) as max_year 
  FROM a 
  GROUP BY a.alias
) b 
ON a.alias=b.alias and a.year=b.max_year

Outline of solution:

1. create a view for each subquery
2. replace subqueries with those views

Here's the solution query:

CREATE VIEW v_max_year AS 
  SELECT alias, MAX(year) as max_year 
  FROM a 
  GROUP BY a.alias;

CREATE VIEW v_latest_info AS 
  SELECT a.* 
  FROM a 
  JOIN v_max_year b 
  ON a.alias=b.alias and a.year=b.max_year;

It works fine on mysql 5.0.45, without much of a speed penalty (compared to executing the original sub-query select without any views).

Couldn't your query just be written as:

SELECT u1.name as UserName from Message m1, User u1 
  WHERE u1.uid = m1.UserFromID GROUP BY u1.name HAVING count(m1.UserFromId)>3

That should also help with the known speed issues with subqueries in MySQL

It appears to be a known issue.

http://dev.mysql.com/doc/refman/5.1/en/unnamed-views.html

http://bugs.mysql.com/bug.php?id=16757

Many IN queries can be re-written as (left outer) joins and an IS (NOT) NULL of some sort. for example

SELECT * FROM FOO WHERE ID IN (SELECT ID FROM FOO2)

can be re-written as

SELECT FOO.* FROM FOO JOIN FOO2 ON FOO.ID=FOO2.ID

or

SELECT * FROM FOO WHERE ID NOT IN (SELECT ID FROM FOO2)

can be

SELECT FOO.* FROM FOO 
LEFT OUTER JOIN FOO2 
ON FOO.ID=FOO2.ID WHERE FOO.ID IS NULL

You can work around this by creating a separate VIEW for any subquery you want to use and then join to that in the VIEW you're creating. Here's an example: http://blog.gruffdavies.com/2015/01/25/a-neat-mysql-hack-to-create-a-view-with-subquery-in-the-from-clause/

This is quite handy as you'll very likely want to reuse it anyway and helps you keep your SQL DRY.

create a view for each subquery is the way to go. Got it working like a charm.