MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

 This post comes from Jaime Crespo at the MySQL Performance Blog.

While preparing the webinar I will deliver this Friday, I ran into a quite interesting (although not very impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10.

This turned into a really nice exercise for checking the performance and inner workings of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed on our blog.

It was the following query in particular that had this surprising outcome:

mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%';

On a table like this:

CREATE TABLE `cast_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) NOT NULL,
  `movie_id` int(11) NOT NULL,
  `person_role_id` int(11) DEFAULT NULL,
  `note` varchar(250),
  `nr_order` int(11) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `role_id_note` (`role_id`,`note`)
) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8;

The table had 22 million rows, with approximately 8 million of them having role_id = 1, and 266 have role_id = 1 and containing the word ‘Jaime’ somewhere in the field note.

The original query had a stable execution time of 1.09 sec, while the following one, which selects less amount of data (just one column) and can take advantage of the covering index technique, did actually take more time to execute:

mysql> SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
266 rows in set (1.82 sec)

Please note that the times were very stable and the contents of the buffer pool did not affect the results.

What was happening? Well, in order to understand it I must provide you with more background information. My buffer pool was big enough to hold the whole database (data and indexes fit completely in memory). Also, I was testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output:

mysql> EXPLAIN SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using index condition
1 row in set (0.00 sec)

With ICP, the actual number of rows read at SQL layer is actually very different from the “rows” value seen above. This is because the second part of the condition -note like '%Jaime%'- is actually tested at engine level, not at handler level.

Condition pushdown is one of the new features of MySQL 5.6, and actually is a great improvement over MySQL 5.5. For example, in this case, the actual number of “Handler_read_next” calls was reduced from 8346769 (5.5) to just 266 (5.6), reducing the executing time by almost 5 times. Pro tip: make sure you always check the Handler status variables for post-execution analysis.

So why is the “SELECT note” actually slower? It seems that whenever the covering index technique is available, this is always preferred over the ICP optimization:

mysql> EXPLAIN SELECT role_id FROM cast_info WHERE role_id = 1 and note like '%Jaime%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cast_info
         type: ref
possible_keys: role_id_note
          key: role_id_note
      key_len: 4
          ref: const
         rows: 10259274
        Extra: Using where; Using index
1 row in set (0.00 sec)

I reported this issue to Oracle and they confirmed that this is the intended/current status of the MySQL 5.6.10 optimizer. Other interesting things to notice:

• ICP is a great new feature that already saved us a lot of execution time, probably its cost has to be tuned better in the feature. There are more ways to make a query faster, which means you need more manual care and tuning now.
• MySQL is conservative about “Using index” -in most cases it will be the right solution because our SELECT will only be faster when the condition is very selective and the buffer pool is effective.
• There is no workaround, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using index”.

So, I wouldn’t call this a bug, and I’m not especially concerned about this particular case — you may or may not consider it an edge case — but I would call it a limitation of the current query planner. However I would like to see better algorithms, statistics computation and monitoring variables about index usage in the future, now that we have more complex optimization strategies. Even row-level operation counters are sometimes not enough.

Do you want to know more about the MySQL 5.6 query optimization improvements in a practical way, with real-life examples? Do you want to know a 3-party, independent and technical opinion about the new features of MySQL query planner? Are you not yet familiar with terms like MRR, BKA or ICP? Are you a Developer or a DBA and want to be prepared for the MySQL 5.6 release, and get advantage of the latest integrated tools that MySQL provides with its last GA release? Then I invite you to join me at the webinar I have prepared for this Friday, March 15: “Learn How MySQL 5.6 Makes Query Optimization Easier”