Bug #71302 Optimizer should consider effect of ICP when determining plans

• Submitted: 5 Jan 2014 15:29
• Modified: 6 Jan 2014 2:21
• Reporter: Morgan Tocker
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.7+
• OS: Any
• CPU Architecture: Any

[5 Jan 2014 15:29] Morgan Tocker

Description:
Via http://www.mysqlperformanceblog.com/2014/01/03/multiple-column-index-vs-multiple-indexes-w...

In a comment, Jørgen Løland mentioned that the optimizer does not consider the effect of ICP when determining plans.

I would like to request "making the optimizer ICP aware" as a feature request.

How to repeat:
From http://www.mysqlperformanceblog.com/2014/01/03/multiple-column-index-vs-multiple-indexes-w...

mysql5.6> EXPLAIN SELECT sum(length(val)) FROM t1000idx2 WHERE j=2 AND i BETWEEN 100 and 200;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1000idx2 | ALL  | ij            | NULL | NULL    | NULL | 1000545 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+

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                 |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+
|  1 | SIMPLE      | t1000idx2 | range | ij            | ij   | 8       | NULL | 188460 | Using index condition |
+----+-------------+-----------+-------+---------------+------+---------+------+--------+-----------------------+

And the difference in response time is impressive:
- Without FORCE INDEX (full table scan): 0.45s
- With FORCE INDEX (multiple column index + index condition pushdown): 0.04s, a 10x improvement!

Suggested fix:
Optimizer Feature Request