July 30, 2014

MySQL Performance – eliminating ORDER BY function

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:

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:

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Lukas says:

    So the lesson to learn is if you always apply a specific function on a column, you might as well apply it before the actual insert so that you can leverage the index properly. Or denormalize ..

  2. Ladislav says:

    Slightly off-topic: talking about order by … trying to find solution for a long time to optimize queries with “order by field” like this:

    EXPLAIN SELECT id, name
    FROM table_name
    WHERE id IN ( 222839, 299872, 301535 )
    ORDER BY FIELD( id, 222839, 299872, 301535 )

    FROM EXPLAIN:
    —————————————
    select_type SIMPLE
    table table_name [innodb]
    type range
    possible_keys PRIMARY
    key PRIMARY
    key_len 4
    ref NULL
    rows 3
    Extra Using where; Using filesort

    Am I plain stupid or is it a bug (in the optimizer) in MySQL ?

  3. Ladislav says:

    Sorry, something’s missing … the problem is the filesort, of course

  4. peter says:

    Why are you surprised you’re sorting by FIELD function – of course MySQL can’t use Index to do such sort.

  5. Karl Ravn says:

    And, if anyone skipped the fine prints in the docs, you can also remove the whole order by-clause, making indexed scans alot faster if the result is rather large, and the order of the rows doesn’t matter. For example statistics or samples of rows. just type ORDER BY NULL (unsure of ansi-compatibility though).. Then the result will be in the order that they are in the database (based on creation, unless you have deleted rows). Even small queries can ease some load on a heavy-traffic db server with this technique.

Speak Your Mind

*