Search code examples
mysqlsql-order-by

Efficient to do math in a MySql "order by" clause?

I've been told on several occasions that it is quite efficient to SELECT using math and that it is NOT very efficient to use math in a WHERE clause. Are these sentiments correct? And how does this apply to ORDER BY clauses?

Thanks!!

Example: 

SELECT a.* FROM a ORDER BY (a.field_1*a.field_2)
Solution

  • Your query will have to sort the entire table using temporary files on disk if the result is larger than the sort_buffer_size.

    You probably want to add a column to your table that holds the value of field1*field2. This of course slightly denormalizes your data, BUT YOU CAN CREATE AN INDEX ON THE FIELD.

    If you have an index on the new field, then MySQL can read the data pre-sorted using the index, because MySQL indexes are b*tree structures and b*tree structures are stored in pre-sorted order. This won't incur extra disk IO or CPU activity for the sort and you will scan the table only once.