MySQL Performance – eliminating ORDER BY function

October 17, 2007

Author

Peter Zaitsev

Insight for Developers

Share this Post:

One of the first rules you would learn about MySQL Performance Optimization is to avoid using functions when comparing constants or order by. Ie use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even if function is very simple such as arithmetic operation. Same can apply to order by, if you would like that to use the index for sorting. There are however some interesting exception.

Compare those two queries for example. If you look only at ORDER BY clause you would see first query which sorts by function is able to avoid order by while second which uses direct column value needs to do the filesort:

<br>mysql> explain select * from tst where i=5 and date(d)=date(now()) order by date(d) G<br>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tst<br>         type: ref<br>possible_keys: i<br>          key: i<br>      key_len: 5<br>          ref: const<br>         rows: 10<br>        Extra: Using where<br>1 row in set (0.00 sec)<br><br>mysql> explain select * from tst where i=5 and date(d)=date(now()) order by d G<br>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tst<br>         type: ref<br>possible_keys: i<br>          key: i<br>      key_len: 5<br>          ref: const<br>         rows: 10<br>        Extra: Using where; Using filesort<br>1 row in set (0.00 sec)<br>

1

mysql> explain select * from tst where i=5 and date(d)=date(now()) order by date(d) G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tst type: ref possible_keys: i key: i key_len: 5 ref: const rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from tst where i=5 and date(d)=date(now()) order by d G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tst type: ref possible_keys: i key: i key_len: 5 ref: const rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec)

If you take a closer look to WHERE clause you will find the reason – date(d) equals to date(now()) which is constant which means we’re sorting by constant and so sort phase can be skipped all together.

Note in this case MySQL Optimizer is rather smart and is able to do this even if we have function in ORDER BY and exactly the same function is equals to constant by WHERE clause. If course it works for direct constants as well.

However if functions are different MySQL is not able to do this optimization even in cases when this would be possible:

<br>mysql> explain select * from tst where i=5 and date(d)=date(now()) order by unix_timestamp(date(d)) G<br>*************************** 1. row ***************************<br>           id: 1<br>  select_type: SIMPLE<br>        table: tst<br>         type: ref<br>possible_keys: i<br>          key: i<br>      key_len: 5<br>          ref: const<br>         rows: 10<br>        Extra: Using where; Using filesort<br>1 row in set (0.00 sec)<br>

1

mysql> explain select * from tst where i=5 and date(d)=date(now()) order by unix_timestamp(date(d)) G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tst type: ref possible_keys: i key: i key_len: 5 ref: const rows: 10 Extra: Using where; Using filesort 1 row in set (0.00 sec)