![]() It relies on the fact that MySQL evaluates the content of the derived table named initialization. To demonstrate it, let’s say this unaware developer just wants to add to our query a column displaying the full name of the country of citizenship – pretty innocent, uh? He first makes a table with full names: Moreover, if later an application developer modifies our query, this modification may lead MySQL to change its optimization strategy and do ordering after evaluating the selected expressions, totally breaking logic. But, as noted, it’s relying on an ORDER BY optimization done by MySQL, which is not guaranteed to remain constant in the future (who knows? we revisit heuristic and cost-based optimization decisions from time to time). Being calculated before ordering, row numbers would have no reason to be according to the order.įortunately, here, MySQL sees that there is only one table and that the ordering expression is a column of this table, so it makes an optimization: it first orders the rows of people, then reads this ordered result and, for each row, calculates selected expressions (including So increases in order, as desired. So, if we follow this, we read people, evaluate selected expressions for each row, so calculate a row number for each row, and finally we order the result by birth date. ![]() ![]() ![]() Yes, in theory ordering is the very final stage of execution, after evaluation of the selected expressions for all matching rows (which is logical, as ORDER BY is allowed to reference selected expressions by alias). Standard SQL says that we should join tables (here we have only one, people), put the result of the join in an imaginary table, then evaluate selected expressions for each row, forming yet another imaginary table, then sort that table and return it to the user. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |