Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries in MySQL

When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.

The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.

For instance, here is a common query which could be inefficient:

SELECT id FROM t WHERE id='1';

And here is the CREATE TABLE output:

mysql> show create table tG
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` decimal(10,0) default NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

You can see it’s a very basic table, and a very basic query.

And looking at the EXPLAIN output, everything still appears normal:

mysql> explain select id from t where id='1';
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | id            | id   | 6       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

However, now let’s look at the EXPLAIN EXTENDED and SHOW WARNINGS output:

mysql> explain extended select id from t where id='1';
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | id            | id   | 6       | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                      |
+-------+------+----------------------------------------------------------------------------------------------+
| Note  | 1003 | select `testing`.`t`.`id` AS `id` from `testing`.`t` where (`testing`.`t`.`id` = _latin1'1') |
+-------+------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In the WHERE clause of the SHOW WARNINGS output, you see this:

where (`testing`.`t`.`id` = _latin1'1')

The _latin1’1′ is key, as it shows the value of 1 is getting cast as a latin1 value. This is because we surrounded the 1 with single quotes in the query.

Note that this does not happen if you do not surround the 1 with single quotes:

mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message                                                                             |
+-------+------+-------------------------------------------------------------------------------------+
| Note  | 1003 | select `testing`.`t`.`id` AS `id` from `testing`.`t` where (`testing`.`t`.`id` = 1) |
+-------+------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

In this example, the constant value of 1 is being cast, which is not horrible, but still not as efficient as it should be. However, depending on how the query is written, I’ve seen where it might choose to cast/convert the row retrived from the database (especially in a JOIN scenario – it will need to convert one column if they differ). These conversions or casts can be very inefficient (especially if within a JOIN or worse, a sub-query), and would not be otherwise caught without using EXPLAIN EXTENDED / SHOW WARNINGS.

And in addition to the above example, it is common to see similar types of conversions occurring if you try to match two columns whose character sets differ. This can be another easy-to-overlook problem, which can lead to poor performance, and you wouldn’t know it wasn’t running efficiently unless you used EXPLAIN EXTENDED (or unless it brings your system to a halt during a heavy load ..).

So in conclusion, consider using EXPLAIN EXTENDED / SHOW WARNINGS in lieu of just EXPLAIN when analyzing your queries.

Note: If you are using pre-5.1.46, you may want to consider running both EXPLAIN and EXPLAIN EXTENDED to ensure the optimization plans match. This is due to the following bug which existed:

http://bugs.mysql.com/bug.php?id=47669


Click here to give this article a “Thumbs Up” on Planet MySQL !

  

Tags: , , , , , ,

One Response to “Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries in MySQL”

  1. [...] This post was mentioned on Twitter by planetmysql. planetmysql said: Using EXPLAIN EXTENDED / SHOW WARNINGS to Help Troubleshoot Inefficient Queries in MySQL: When examining the execu… http://bit.ly/alQYGN [...]


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty