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:
- create a view for each subquery
- 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 executingthe 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