MySQL query / clause execution order

问题:

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

回答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 clause
• WHERE clause
• SELECT clause
• GROUP BY clause
• HAVING clause
• ORDER 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).

回答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

回答3:

I think the execution order is like this:

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>[, <offset_number>]