EXPLAIN CONDITIONS patch available

I’ve made a patch that makes EXPLAIN show conditions that are attached to various points of the query plan. If you run EXPLAIN CONDITIONS (or EXPLAIN CONDS) statement, the output besides the usual EXPLAIN resultset will have a second resultset that will show

  • Conditions attached to individual tables
  • Conditions that are applied before/after join buffering
  • Table and index conditions that were pushed down into the storage engine
  • … and so forth (I believe it prints out all possible conditions that are there)

It looks like this:

mysql> explain conds select * from City, Country where City.Country=Country.Code and City.Name like 'C%' and Country.Continent='Asia' and Country.Population>5000000;
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
| id | select_type | table   | type | possible_keys     | key       | key_len | ref             | rows | Extra                              |
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
|  1 | SIMPLE      | Country | ref  | PRIMARY,Continent | Continent | 21      | const           |    1 | Using index condition; Using where |
|  1 | SIMPLE      | City    | ref  | Country           | Country   | 3       | db.Country.CODE |   18 | Using where                        |
+----+-------------+---------+------+-------------------+-----------+---------+-----------------+------+------------------------------------+
2 rows in set (0.01 sec)

+—-+———+—————–+——————————–+
| id | table   | cond_type       | cond                           |
+—-+———+—————–+——————————–+
|  1 | Country | pushed_idx_cond | (Country.Continent = ‘Asia’)   |
|  1 | Country | where           | (Country.Population > 5000000) |
|  1 | City    | where           | (City.`Name` like ‘C%’)        |
+—-+———+—————–+——————————–+ 
3 rows in set (0.01 sec)

Unlike EXPLAIN EXTENDED, EXPLAIN CONDS doesn’t use excessive quoting or database prefixes before all columns. Excessive parentheses are still there, I have intent to remove them.

How you can get it:

Both the branch and the patch are made against mysql-6.0 tree. The code has some intersection with new 6.0 features, eg. it prints pushed index conditions, which are in 6.0 only, so the patch can’t be automatically applied to MySQL-5.x. The conflicts should be trivial though, the downport should be a question of removing all parts of the patch that break the compilation. If you need EXPLAIN CONDS in 5.x but can’t manage the downport, please let me know, perhaps I’ll be able to lend a hand.

Posted in Uncategorized on September 2nd, 2008 by spetrunia | |

3 Responses to ' EXPLAIN CONDITIONS patch available '

Subscribe to comments with RSS or TrackBack to ' EXPLAIN CONDITIONS patch available '.

  1. Xaprb said,

    on September 2nd, 2008 at 6:53 pm

    Awesome!

  2. Rob Wultsch said,

    on September 3rd, 2008 at 6:35 pm

    Very cool. This could be very helpful when dealing with not well know tables and horrifically formatted and incredibly long queries.


  3. on September 5th, 2008 at 8:47 pm

    […] Sergey Petrunia makes his EXPLAIN CONDITIONS patch available. He writes, “I’ve made a patch that makes EXPLAIN show conditions that are attached to various points of the query plan.” […]

Leave a reply

You must be logged in to post a comment.