Query optimization in MySQL with Subqueries
gergerconsulting.blogspot.com
Query optimization in MySQL with Subqueries
1–10 of 15 posts
Re: Query optimization in MySQL with Subqueries
#2Getting rid of the 'IN' clause.
Re: Query optimization in MySQL with Subqueries
#3I'm reading the first query and maybe it's just early but why isn't that subquery just a join? It seems to be acting that way.
Re: Query optimization in MySQL with Subqueries
#4I'm reading the first query and maybe it's just early but why isn't that subquery just a join? It seems to be acting that way.
Good point! Actually, in early stages of our development, it was accomplished using two different queries and we needed to combine them to a single query. It was just a scratch and maybe it caused a bias which prevented me to see shorter way.
Re: Query optimization in MySQL with Subqueries
#5Getting rid of the 'IN' clause.
exactly. Using an IN statement with a subquery is textbook "how to make a query slow" - it forces a sequential scan. No-one who understands SQL would write a query like that.
Re: Query optimization in MySQL with Subqueries
#6There'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.
Re: Query optimization in MySQL with Subqueries
#7Getting rid of the 'IN' clause.
exactly. Using an IN statement with a subquery is textbook "how to make a query slow" - it forces a sequential scan. No-one who understands SQL would write a query like that.
It's certainly a cardinal sin on MySQL but commercial platforms have optimizations that make these correlated subqueries performant.
Re: Query optimization in MySQL with Subqueries
#8Re: Query optimization in MySQL with Subqueries
#9I'm reading the first query and maybe it's just early but why isn't that subquery just a join? It seems to be acting that way.
It basically is; the technique used here is sometimes called a delayed join, which is also extremely effective on huge sorted queries.
Re: Query optimization in MySQL with Subqueries
#10Isn'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.