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:

• I’ve put a branch on launchpad: lp:~sergefp/mysql-server/mysql-6.0-explain-conds. You need to get bazaar and then do bzr branch lp:~sergefp/mysql-server/mysql-6.0-explain-conds
• The patch itself can be found here: lists.mysql.com/commits/52981.

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.