July 31, 2014

How to find MySQL queries worth optimizing ?

One question I often get is how one can find out queries which should be optimized. By looking at pt-query-digest report it is easy to find slow queries or queries which cause the large portion of the load on the system but how do we know whenever there is any possibility to make this query run better ? The full answer to this question will indeed require complex analyses as there are many possible ways query can be optimized. There is however one extremely helpful metric which you can use – ratio between rows sent and rows analyzed. Lets look at this example:

The query in this case has sent zero rows (as there are no matches) but it had to examine 10Mil rows to produce result. What would be good scenario ? – query examining same amount of rows as they end up sending. In this case if I index the table I get the following record in the slow query log:

Rows_examined=0 same as Rows_sent meaning this query is optimized quite well. Note you may be thinking in this case there is no database access happening at all – you would be wrong. The index lookup is being perform but as only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains zero.

It looks simple so far but it also a huge oversimplification. You can do such simple math only to the queries without aggregate functions/group by and only to ones which examine one table only. What is about queries which query more than one table ?

In this case we actually join 2 tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of “real” access to the data it will:

In this case we have 2 rows analyzed for each row set which is expected as we have 2 (logical) tables used in the query.

This rule also does not work if you have any group by in the query:

This only sends 2 rows while scanning 10 million, while we can’t really optimize this query in a simple way because scanning all that rows are actually needed to produce group by results.
What you can think about in this case is removing group by and aggregate functions. Then query would become “select * from sbtest” which would send all 10M rows and hence there is no ways to simply optimize it.

This method does not only provide you with “yes or no” answer but rather helps to understand how much optimization is possible. For example I might have query which uses some index scans 1000 rows and sends 10… I still might have opportunity to reduce amount of rows it scans 100x, for example by adding combined index.

So what is the easy way to see if query is worth optimizing ?
- see how many rows query sends after group by, distinct and aggregate functions are removed (A)
- look at number of rows examined divided by number of tables in join (B)
- if B is less or equals to A your query is “perfect”
- if B/A is 10x or more this query is a very serious candidate for optimization.

This is simple method and it can be used with pt-query-digest very well as it reports not only average numbers but also the outliers.

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. The count(*) group by k could be optimized slightly by adding an index to k. That will at least give you a full index scan instead of a full table scan which can reduce io if the table is wide.

  2. Justin…. yes. There are many things including sorting covering indexes etc which it does not cover. The statement here is perhaps – queries benefit from reducing number of rows they examine, everything else being equal. And also what in the perfect situation you can have 1 row examined for row sent

  3. khan says:

    How do you see the following query that doesn’t examine or send any rows but still takes longer?

    # Query_time: 21 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
    delete from tbl where id < 4803304003 AND eventtime EXPLAIN select * from alertdata where eventid < 4803304003 AND eventtime < '20120906000000' limit 100000;
    +—-+————-+———–+——-+————————-+———+———+——+——+————-+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +—-+————-+———–+——-+————————-+———+———+——+——+————-+
    | 1 | SIMPLE | tbl | range | PRIMARY,eventtime_index | PRIMARY | 8 | NULL | 1 | Using where |
    +—-+————-+———–+——-+————————-+———+———+——+——+————-+
    1 row in set (0.00 sec)

  4. hron84 says:

    @khan I think delete is a little special, it will not send or examine rows.

    What coming in my mind is the eventtime itself. As I see it is a string. However, I think it can be better if that column become a DATETIME or INTEGER (unix timestamp) value, because scanning on these rows can be faster.

  5. khan says:

    @hron84, eventtime is already stored as INTEGER.

  6. MatteoSp says:

    Cool,
    can you give a little advice on how to analyze an AWS RDS instance logs?

    thanks
    m.

  7. Gleb Deykalo says:

    Hi Peter,

    Could you please help me with some query execution plan? It looks pretty simple at first glance, but…

    The table is very simple:

    CREATE TABLE test_idx (
    a int(11) DEFAULT NULL,
    b int(11) DEFAULT NULL,
    c int(11) DEFAULT NULL,
    KEY idx_cover_all (a,b,c)
    ) ENGINE=InnoDB;

    Test data:
    insert into test_idx values (1, 1, 1), (2, 2, 2), (3, 3, 3), (1, 2, 3), (3, 2, 1), (2, 3, 1), (2, 1, 3), (1, 3, 2), (1, 2, 2);

    When I ask for an exact row, it uses index and join type is REF:

    mysql> explain select c from test_idx where a = 2 and b = 2 and c = 2\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_idx
    type: ref
    possible_keys: idx_cover_all
    key: idx_cover_all
    key_len: 15
    ref: const,const,const
    rows: 1
    Extra: Using where; Using index

    (1) But why do I see “Using where”? I can not understand why engine (InnoDB) can not filter row inside.

    When I ask for a rows with little more complicated query, it confuses me even more:
    mysql> explain select c from test_idx where a = 2 and b = 2 and c IN (1, 2, 3)\G
    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: test_idx
    type: range
    possible_keys: idx_cover_all
    key: idx_cover_all
    key_len: 15
    ref: NULL
    rows: 3
    Extra: Using where; Using index

    Join type “range”, but I know that MySQL uses it for IN queries even if it is not actually range, so it is OK. But…

    (2) No reference fields — why?
    (3) Still can see “User where” — why?

    Why InnoDB can not be sure it found exactly correct row? Or is it MySQL server does not believe InnoDB and want to check data on its side?

    I can reproduce it both on Percona Server 5.1 and Percona Server 5.5
    5.1.54-rel12.5-log Percona Server with XtraDB (GPL), Release 12.5, Revision 188
    5.5.21-55 Percona Server (GPL), Release 25.1

Speak Your Mind

*