MySQL 5.6 makes multiple column indexes more efficient than before with index condition pushdown.īut don’t forget that the optimizer is not perfect: you may have to use index hints to benefit from this feature. Multiple column index vs multiple indexes? Having indexes on single columns often lead to the optimizer using the index_merge access type, which is typically not as good as accessing a single index on multiple columns. If we look at the optimizer trace for the query with the FORCE INDEX hint, ICP is only detected after the range scan is chosen: This probably explains why the cost of the range scan is overestimated. Now we will see how the optimizer estimates the cost of the range scan using the ij index: Īt this stage the optimizer does not know if ICP can be used.
Mysql optimizer trace full#
This is the estimated cost for a full table scan. Mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE
Mysql> SELECT sum(length(val)) FROM T WHERE j=2 AND i BETWEEN 100 and 200 The optimizer trace sheds some light: mysql> SET optimizer_trace="enabled=on" It is interesting to see that the optimizer fails to find the best execution plan for this simple query. – With FORCE INDEX (multiple column index + index condition pushdown): 0.04s, a 10x improvement! Additional thoughts
– Without FORCE INDEX (full table scan): 0.45s This time ICP is used (see “Using index condition” in the Extra field)!Īnd the difference in response time is impressive: | 1 | SIMPLE | t1000idx2 | range | ij | ij | 8 | NULL | 188460 | Using index condition | | 1 | SIMPLE | t1000idx2 | ALL | ij | NULL | NULL | NULL | 1000545 | Using where |Īnd what if we use FORCE INDEX? mysql5.6 > EXPLAIN SELECT sum(length(val)) FROM t1000idx2 FORCE INDEX(ij) WHERE j=2 AND i BETWEEN 100 and 200 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | Unfortunately the optimizer still prefers the full table scan which gives us the same bad response time: mysql5.6> EXPLAIN SELECT sum(length(val)) FROM t1000idx2 WHERE j=2 AND i BETWEEN 100 and 200
On MySQL 5.6, this query is a good candidate for index condition pushdown (ICP), so we can reasonably hope that response time for t1000idx2 will improve. While for t1000merge, the index on (j) is an obvious good candidate to filter efficiently.Ĭonsequently this query has a better response on t1000merge ( 0.01s) than on t1000idx2 ( 0.45s). Why this specific query? With MySQL 5.5, for t1000idx2, the optimizer estimates that the index on (i,j) is not selective enough and it falls back to a full table scan. We will look at this query on MySQL 5.5.35 and MySQL 5.6.15: SELECT sum(length(val)) FROM T WHERE j=2 AND i BETWEEN 100 and 200 The buffer pool is large enough to hold all data and indexes. Tables were populated with 1M rows for this test, i and j have 1000 distinct values (independent of each other). Id int not null auto_increment primary key, But with all the recent optimizer improvements, is there anything different with MySQL 5.6? Setupįor this test, we will use these 2 tables (same structure as in Peter’s post): CREATE TABLE t1000merge ( A question often comes when talking about indexing: should we use multiple column indexes or multiple indexes on single columns? Peter Zaitsev wrote about it back in 2008 and the conclusion then was that a multiple column index is most often the best solution.
0 kommentar(er)
