April 2nd, 2013, 07:07 PM
Which part of the query gets executed first?
In the query below would it be correct to say this is the order of execution? Please note this may not be the case if the sub query is not corrrelated.
1 - FROM entries in the main query
2 - SELECT category, title, created
3 - WHERE (gets tricky):
4 - SELECT MAX(created) in the sub query executes "more than once if neccessary"! Matching each and every single result of the main query with the subquery. For example every row returned from main query has to be checked against the subquery. In other words the subquery runs as many times as the results of the main query. If there are 10 rows returned from the main query, the sub query has to evaluate each row and check if it is the MAX(created) or not. This may not be the case if the sub query is not corrrelated..
FROM entries AS t
WHERE created = (SELECT Max(created)
WHERE category = t.category)
Last edited by zxcvbnm; April 2nd, 2013 at 07:17 PM.
April 2nd, 2013, 07:25 PM
using your numbers, the order of execution is 1, 3, 4, 2
SELECT is the last clause executed before ORDER BY
you are correct that a correleated subquery is executed once for each row in the main query
just for the sake of interest, what does an EXPLAIN on your query produce?
April 2nd, 2013, 09:58 PM
1 - How is 1,3,4,2 possible if the correleated subquery is executed once for each row retrieved by the main query? Main query results shoud be retrieved before the subquery is executed for each returned row, no? Otherwise sunquery won't find any matches because thre are no results yet from the main query.
Originally Posted by r937
2 - Here is the EXPLAIN. I sometimes craving a section in your book speaking about EXPLAIN in details.
3 - I am pretty sure you are gonna give negative feedback on this but what about this:
WHERE id IN (SELECT id
GROUP BY category
ORDER BY created DESC)
April 2nd, 2013, 11:29 PM
what about it?
Originally Posted by zxcvbnm
by the way, the ORDER BY in the subquery has no purpose and is ignored