MySQL query / clause execution order
Solution 1
The actual execution of MySQL statements is a bit tricky. However, the standard does specify the order of interpretation of elements in the query. This is basically in the order that you specify, although I think HAVING
and GROUP BY
could come after SELECT
:
FROM
clauseWHERE
clauseSELECT
clauseGROUP BY
clauseHAVING
clauseORDER BY
clause
This is important for understanding how queries are parsed. You cannot use a column alias defined in a SELECT
in the WHERE
clause, for instance, because the WHERE
is parsed before the SELECT
. On the other hand, such an alias can be in the ORDER BY
clause.
As for actual execution, that is really left up to the optimizer. For instance:
. . .
GROUP BY a, b, c
ORDER BY NULL
and
. . .
GROUP BY a, b, c
ORDER BY a, b, c
both have the effect of the ORDER BY
not being executed at all -- and so not executed after the GROUP BY
(in the first case, the effect is to remove sorting from the GROUP BY
and in the second the effect is to do nothing more than the GROUP BY
already does).
Solution 2
This is how you can get the rough idea about how mysql executes the select query
DROP TABLE if exists new_table;
CREATE TABLE `new_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`testdecimal` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`id`));
INSERT INTO `new_table` (`testdecimal`) VALUES ('1234.45');
INSERT INTO `new_table` (`testdecimal`) VALUES ('1234.45');
set @mysqlorder := '';
select @mysqlorder := CONCAT(@mysqlorder," SELECT ") from new_table,(select @mysqlorder := CONCAT(@mysqlorder," FROM ")) tt
JOIN (select @mysqlorder := CONCAT(@mysqlorder," JOIN1 ")) t on ((select @mysqlorder := CONCAT(@mysqlorder," ON1 ")) or rand() < 1)
JOIN (select @mysqlorder := CONCAT(@mysqlorder," JOIN2 ")) t2 on ((select @mysqlorder := CONCAT(@mysqlorder," ON2 ")) or rand() < 1)
where ((select @mysqlorder := CONCAT(@mysqlorder," WHERE ")) or IF(new_table.testdecimal = 1234.45,true,false))
group by (select @mysqlorder := CONCAT(@mysqlorder," GROUPBY ")),id
having (select @mysqlorder := CONCAT(@mysqlorder," HAVING "))
order by (select @mysqlorder := CONCAT(@mysqlorder," ORDERBY "));
select @mysqlorder;
And here is the output from above mysql query, hope you can figure out the mysql execution of a SELECT query :-
FROM JOIN1 JOIN2 WHERE ON2 ON1 ORDERBY GROUPBY SELECT WHERE ON2 ON1 ORDERBY GROUPBY SELECT HAVING HAVING
ericsicons
Updated on July 25, 2022Comments
-
ericsicons over 1 year
What is the predefined order in which the clauses are executed in MySQL? Is some of it decided at run time, and is this order correct?
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
-
ericsicons almost 10 yearsalias can be used in GROUP BY so I guess it is parsed after select. (SELECT name as n, count(name) FROM test group by n) works
-
Gordon Linoff almost 10 years@ericsicons . . . Thank you. I missed that.
-
Shafizadeh about 8 yearsAre you sure
FROM
is first one? I thinkWHERE
is the first, Consider this:SELECT * FROM table WHERE false
. I think it will be faster ifWHERE
clause be executed sooner thanFROM
. -
Gordon Linoff about 8 years@Shafizadeh . . . The interpretation of the query starts with the
FROM
clause. The execution path is determined by the optimizer. -
Matthew Read about 7 yearsWhy do you "think" that? What's your source?
-
pekechis about 7 yearsThis is no the order of execution. This is the writing order...nothing to do with the execution order.
-
ReinstateMonica3167040 over 6 yearsI think
SELECT
should be 5th -
Sayakiss over 4 yearsCould you please give the exact page of standard states the interpretation order? I downloaded a draft of
SQL:2003
, but found nothing about that order -
flow2k almost 4 years@GordonLinoff You say "the standard does specify..." Would you please provide a reference to this?
-
Gordon Linoff almost 4 years@flow2k . . . This question is about MySQL. There is actually no reason to go to the standard, because MySQL is quite explicit about the ordering: dev.mysql.com/doc/refman/8.0/en/select.html.