One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation as well as what other notes optimizer may wish to tell us. It is best seen by example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> explain extended select * from sbtest where id>5 and id>6 and c="a" and pad=c; +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+ | 1 | SIMPLE | sbtest | range | PRIMARY | PRIMARY | 4 | NULL | 422510 | Using where | +----+-------------+--------+-------+---------------+---------+---------+------+--------+-------------+ 1 row in set, 1 warning (0.02 sec) mysql> show warnings \G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`sbtest`.`id` AS `id`,`test`.`sbtest`.`k` AS `k`,`test`.`sbtest`.`c` AS `c`,`test`.`sbtest`.`pad` AS `pad` from `test`.`sbtest` where ((`test`.`sbtest`.`id` > 5) and (`test`.`sbtest`.`id` > 6) and (`test`.`sbtest`.`c` = _utf8'a') and (`test`.`sbtest`.`pad` = _utf8'a')) 1 row in set (0.00 sec) |
As you see after query transformation “*” was expanded to full column list, each column was fully quantified plus MySQL optimizer converted
c=”a” and pad=c; to c=”a” and pad=”a”. MySQL optimizer could not however catch id>5 and id>6 can be converted just to id>6.
Lets see another example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain extended select t1.id,t2.pad from sbtest t1, sbtest t2 where t1.id=5 and t2.k=t1.k; +----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------+ | 1 | SIMPLE | t1 | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | t2 | ref | k | k | 4 | const | 55561 | | +----+-------------+-------+-------+---------------+---------+---------+-------+-------+-------+ 2 rows in set, 1 warning (0.00 sec) mysql> show warnings \G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`t1`.`id` AS `id`,`test`.`t2`.`pad` AS `pad` from `test`.`sbtest` `t1` join `test`.`sbtest` `t2` where ((`test`.`t2`.`k` = `test`.`t1`.`k`) and (`test`.`t1`.`id` = 5)) 1 row in set (0.00 sec) |
In this case we can see table t1 is accessed using “const” access type, which means it should pre-read value for “t1.k” and place it into the query. This transformation however is not shown by EXTENDED EXPLAIN for some reason, while it would be quite helpful.
Finally lets look at third example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql> explain extended select * from sbtest where id in (select id from sbtest where id between 1 and 10); +----+--------------------+--------+-----------------+---------------+---------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------+-----------------+---------------+---------+---------+------+---------+--------------------------+ | 1 | PRIMARY | sbtest | ALL | NULL | NULL | NULL | NULL | 1000109 | Using where | | 2 | DEPENDENT SUBQUERY | sbtest | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where | +----+--------------------+--------+-----------------+---------------+---------+---------+------+---------+--------------------------+ 2 rows in set, 1 warning (0.03 sec) mysql> show warnings \G *************************** 1. row *************************** Level: Note Code: 1003 Message: select `test`.`sbtest`.`id` AS `id`,`test`.`sbtest`.`k` AS `k`,`test`.`sbtest`.`c` AS `c`,`test`.`sbtest`.`pad` AS `pad` from `test`.`sbtest` where <in_optimizer>(`test`.`sbtest`.`id`,<exists>(<primary_index_lookup>(<cache>(`test`.`sbtest`.`id`) in sbtest on PRIMARY where (`test`.`sbtest`.`id` between 1 and 10)))) 1 row in set (0.00 sec) |
As you see in this case there is planty of extra information in the message. We can see query is using
Unfortnuately there is yet not too much does available about what each of these means so you should guess from names or take a look at the sources.
To note about last query – even if it looks like there is a whole big deal of optimization being done query is executed extremely poor, completing in 16 seconds, while if you just run this subquery manually and use IN (1,2,3,…10) it completes in tiny fraction of the second.
Anyway EXPLAIN EXTENDED is very valuable addition to EXPLAIN for MySQL Performance troubleshooting.
Peter, a small correction: EXPLAIN EXTENDED was introduced in 4.1, not in 5.0 (and it is documented at http://dev.mysql.com/doc/refman/4.1/en/explain.html)
Thank you Sergey, Now updated.
I forgot in which version it was added.
Thanks Peter — blogged in this week’s Log Buffer.
Thank you very much for your explaination.
best regars
Realy great example.
thank you very much.
Thanks for the great article Peter.
The paragraph following the last example has some HTML problems. It should read:
As you see in this case there is planty of extra information in the message. We can see query is using <in_optimizer>, it is converted to <exists> using <primary key=”” lookup=””>. And there is some <cache> in being done.
HTH,
– AAA
Nice feature. Just need to learn how this really can help in optimization.. Thanks for sharing
Planty = plenty