Re:Query optimization in MySQL with Subqueries
by encoderer on Friday February 11, 2011 @10:19AM
(#2206559)
There's no question that correlated subqueries are piss-poor in MySQL and instead creating derived tables is quicker.
And in most cases it'll be quick enough.
But I have exeprience recently on much larger datasets -- always over a million and often upwards of 10 Million -- and the truth is, 2 queries is better.
I didn't read the SQL closely enough but most cases of subquery (whether in the select, from or where clauses) is to do in 1 query what really takes 2. So assuming that applies here, yeah, if you've got large datasets, doing several simple queries is better than fewer complex queries.
The MySQL query optimizer is just not there. It's not game-day ready the way Oracle and MSSQL is.
And in most cases it'll be quick enough.
But I have exeprience recently on much larger datasets -- always over a million and often upwards of 10 Million -- and the truth is, 2 queries is better.
I didn't read the SQL closely enough but most cases of subquery (whether in the select, from or where clauses) is to do in 1 query what really takes 2. So assuming that applies here, yeah, if you've got large datasets, doing several simple queries is better than fewer complex queries.
The MySQL query optimizer is just not there. It's not game-day ready the way Oracle and MSSQL is.