CODEWITHSUNDEEP
mysqlcachingsortingsql-order-by
Bohemian
Bohemian

Reputation: 425258

Are calculated values used in ORDER BY ever recalculated during sorting?

Suppose we have a query that orders by a calculated value that is not selected, for example:

select * from table
order by col1 * col2 - col3

During typical sorting operations in most languages, it is common for the sort value to be used multiple times during the sort, as a given record is compared to potentially many other records. It is possible that mysql has such an implementation.

Can anyone say definitively if mysql calculates such values once per row and stores them temporarily while the sort completes, or if the values are recalculated whenever a comparison is made (which may be 1-n times)?

I have tagged this mysql, but I would welcome comments/answers regarding/including other popular databases

Upvotes: 3

Views: 132

Answers (2)

Bohemian
Bohemian

Reputation: 425258

And the answer is... mysql executes the calculation once per row.

Due to lack of credible answers, I ran a definitive test on sqlfiddle that orders by the result of a not deterministic function (that must be called every time it's compared) that also records in another table when it has been called. It shows that the number of times called = number of rows.

Upvotes: 1

JayDM
JayDM

Reputation: 1186

I just used the MySQL query browser to explain a query with sorting on a calculation of several columns and it said that it used file sort...So it would appear that it uses a temporary index (calculate one time).

If you have a particular example that you wanted to test - you could make sure that it did the same for you.

Here is a link to one of the developer's blogs that details how order by works:

http://s.petrunia.net/blog/?p=24

Upvotes: 0

Related Questions