Doing math efficiently in the "order" section of MySql?

Question

They told me several times that it’s quite efficient to use SELECT with the help of mathematics and that it is not very efficient to use mathematics in the WHERE clause. Are these moods right? And how does this apply to ORDER BY clauses?

Thanks!!

Example:

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

Answer 1

Your query will need to sort the entire table using temporary files on disk if the result is greater than sort_buffer_size.

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

If you have an index in a new field, then MySQL can read data pre-sorted using the index, since MySQL indexes are fractional structures b *, and tree structures b * are stored in pre-sorted order. This will not increase disk I / O or processor sorting activity, and you only scan the table once.

Answer 2

In order for MySQL to sort the results by the calculated value, it really needs to calculate the value "on the fly" after it has filtered the rows based on the WHERE . If the result set is large enough, MySQL will have to calculate the results for all rows.

For a small set of results, this should be good. However, the larger your result set (before applying LIMIT ), the more calculations the server needs to do, just calculates the value to arrange the strings. If the calculation is deterministic, you should cache this in a column in the result set, and then index it. If it is on the fly, you will need to make sure that your processor is suitable for the task.

In this case, I would recommend creating a.field_3 column and storing the result in it (a.field_1*a.field_2) . Whenever the values ​​of a.field_1 or a.field_2 change, you will need to recalculate the result.

Answer 3

This is a good idea, but I never think that using a mathematical function in an ORDER BY expression makes sense.

You can use this with an alias: -

 select *,(intId * intId)as xalias from m_xxx_list order by xalias; 

OR

 select * from m_xxx_list order by (intId + intId); 

Yes, if you are using the mathematical aggregate function MYSQL, check it out.