Re:Query optimization in MySQL with Subqueries
by TimJYoung on Friday February 11, 2011 @12:33PM
(#2207274)
Isn't the first query generating a cartesian product ?
select e., u., l.*
from entry e, user u, location l
where e.location in (select l2.from location l2 where (l2.lat between x1 and x2) and (l2.lng between y1 and y2)) and e.user = u.id
order by e.id desc
limit 0,10
Where's the join condition for the Location table in the outer query ? Furthermore, what the heck does this mean:
select l2.from location
It looks like it's missing a column name, or a * wildcard.
I was pretty surprised by the results, though. Even our lowly product ElevateDB can handle optimizing correlated sub-queries like this pretty easily, and effectively treating them just like an inner join. I suspect that the query was specified incorrectly, although I guess it's certainly possible that MySQL is very bad at optimizing correlated sub-queries.
select e., u., l.* from entry e, user u, location l where e.location in (select l2.from location l2 where (l2.lat between x1 and x2) and (l2.lng between y1 and y2)) and e.user = u.id order by e.id desc limit 0,10
Where's the join condition for the Location table in the outer query ? Furthermore, what the heck does this mean:
select l2.from location
It looks like it's missing a column name, or a * wildcard.
I was pretty surprised by the results, though. Even our lowly product ElevateDB can handle optimizing correlated sub-queries like this pretty easily, and effectively treating them just like an inner join. I suspect that the query was specified incorrectly, although I guess it's certainly possible that MySQL is very bad at optimizing correlated sub-queries.