Catch the webinar: “Learn How MySQL 5.6 Makes Query Optimization Easier” for more tips on the 5.6 optimizer
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:
1 | mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like '%Jaime%'; |
On a table like this:
1 2 3 4 5 6 7 8 9 10 11 | 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:
1 2 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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”
Hi, Jaime
I’ve noticed this problem before. And after read the source code, i found that the optimizer in MySQL 5.6 can’t use Index Condition Pushdown and Index Coverage Scan simultaneously.
I thought this is a bug, and look forward it to be fixed.
He Dengcheng
Jamie,
Interesting case indeed. From “rows accessed” standpoint it does not make sense to use ICP and Covering Index at the same time as Covering Index already means all where clauses will be checked without reading rows (from Index only).
The difference in performance comes from the fact where the checks are made – by the storage engine itself or at SQL level. I think with well design system there should not be much difference here to begin with.
One solution is of course to have ICP working with Covering Index which will handle this specific case. The better solution though might be to optimize the integration to reduce amount of conversion which has to handle for processing.
As I remember MySQL still uses MyISAM row format internally handling the data even though Innodb is now by far most used engine – so row and index entries have to be copied to be converted from a way innodb stores them in the buffer pool to the MyISAM like format which is pretty expensive. A better solution perhaps would be to have SQL level to be able to perform comparison on the data directly in Innodb buffer pool without copying and conversion and only copy row if it is going to be part of result set.