Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
None
-
None
Description
MariaDB version >= 5.5.23 does not use index condition pushdown for range query, when it should.
I have tested it with both 5.5.23 and 5.5.27. The same query when run against MySQL 5.6.6-m9 and MariaDB 5.3.5 results in index condition pushdown being used.
– Test case:
CREATE TABLE `icp_test` ( |
`i` int(11) NOT NULL, |
`k` int(11) NOT NULL, |
`v` char(32) DEFAULT NULL, |
KEY `k` (`k`,`i`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
|
SOURCE icp_test.dump
|
|
|
EXPLAIN SELECT * FROM icp_test WHERE k BETWEEN 4000 AND 5000 AND i < 1000 ORDER BY k ASC LIMIT 10\G |
The data to use with the table is attached with this bug report.
Explain output is seen on MariaDB 5.5.27:
MariaDB [test]> explain select * from icp_test where k between 4000 and 5000 and i < 1000 order by k asc limit 10\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: icp_test
|
type: range
|
possible_keys: k
|
key: k
|
key_len: 8
|
ref: NULL
|
rows: 222436
|
Extra: Using where
|
1 row in set (0.00 sec)
|
Explain output as seen on MySQL 5.6.6-m9:
mysql> explain select sql_no_cache * from icp_test where k between 4000 and 5000 and i < 1000 order by k asc limit 10\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: icp_test
|
type: range
|
possible_keys: k
|
key: k
|
key_len: 8
|
ref: NULL
|
rows: 222436
|
Extra: Using index condition
|
1 row in set (0.00 sec)
|
Explain output as seen on MariaDB 5.3.5:
MariaDB [test]> explain select sql_no_cache * from icp_test where k between 4000 and 5000 and i < 1000 order by k asc limit 10\G
|
*************************** 1. row ***************************
|
id: 1
|
select_type: SIMPLE
|
table: icp_test
|
type: range
|
possible_keys: k
|
key: k
|
key_len: 8
|
ref: NULL
|
rows: 223490
|
Extra: Using index condition
|
1 row in set (0.00 sec)
|
This obviously means that the above query executes slowly on MariaDB 5.5.27.
The query times on MariaDB 5.3.5 and MySQL 5.6.6-m9:
+-----+------+----------------------------------+
|
| i | k | v |
|
+-----+------+----------------------------------+
|
| 162 | 4010 | 0fcaae4367d0090810a291111b694709 |
|
| 729 | 4016 | 8b52c5ceff7638e0522f7d3e23618736 |
|
| 370 | 4032 | 7235547a596a1db3013d872cdbc7231a |
|
| 707 | 4035 | 7f55c7c738ed3b56a91501c9fb59afe1 |
|
| 645 | 4047 | e265b948627662ac3df9dcd373933335 |
|
| 757 | 4051 | 9cdd8a926d21433e2af26536b078480c |
|
| 572 | 4100 | f3daf307a6889403313ed700e27449b0 |
|
| 89 | 4143 | 2cf9585a3ec980327790bd677fd97aa1 |
|
| 321 | 4184 | 4fbc60b0aa1ebb58864ad0bdbacf6f14 |
|
| 751 | 4185 | 0d47c6f6f50b78fb8b7d80d1c3e45937 |
|
+-----+------+----------------------------------+
|
10 rows in set (0.00 sec)
|
The query time on MariaDB 5.5.27:
+-----+------+----------------------------------+
|
| i | k | v |
|
+-----+------+----------------------------------+
|
| 162 | 4010 | 0fcaae4367d0090810a291111b694709 |
|
| 729 | 4016 | 8b52c5ceff7638e0522f7d3e23618736 |
|
| 370 | 4032 | 7235547a596a1db3013d872cdbc7231a |
|
| 707 | 4035 | 7f55c7c738ed3b56a91501c9fb59afe1 |
|
| 645 | 4047 | e265b948627662ac3df9dcd373933335 |
|
| 757 | 4051 | 9cdd8a926d21433e2af26536b078480c |
|
| 572 | 4100 | f3daf307a6889403313ed700e27449b0 |
|
| 89 | 4143 | 2cf9585a3ec980327790bd677fd97aa1 |
|
| 321 | 4184 | 4fbc60b0aa1ebb58864ad0bdbacf6f14 |
|
| 751 | 4185 | 0d47c6f6f50b78fb8b7d80d1c3e45937 |
|
+-----+------+----------------------------------+
|
10 rows in set (0.73 sec)
|
This is because MariaDB 5.5.27 is reading more data (Handler_read_* = 19328), while MySQL 5.6.6-m9 and MariaDB 5.3.5 are reading far fewer data (Handler_read_* = 10)