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.

3 Comments

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

    Like

Leave a Reply to Xaprb Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s