Details
Minor
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Unexpected index condition pushdown behaviour on range queries with InnoDB plugin
(assignment was a mistake)
ICP is not implemented in InnoDB plugin 5.5, so not surprisingly it doesn't show up in the explain.
Re: Unexpected index condition pushdown behaviour on range queries with InnoDB plugin
Elena,
So to be able to use the new optimisations in MariaDB 5.5, I need to build with XtraDB? But this is not mentioned in the docs. Also the docs mention that MariaDB 5.5 by default uses XtraDB, so I do not expect mtr to be running with InnoDB by default!
Re: Unexpected index condition pushdown behaviour on range queries with InnoDB plugin
Elena,
As for my comment in bug #1000051, I can see that why ICP is not being used with MariaDB 5.5.23, because mtr would by default disable builtin InnoDB (which is actually XtraDB) and use the InnoDB plugin instead (unexpected behaviour).
However, the test case does not force MySQL 5.6.6-m9 to use ICP as well.
Re: Unexpected index condition pushdown behaviour on range queries with InnoDB plugin
Hi,
>> So to be able to use the new optimisations in MariaDB 5.5, I need to build with XtraDB?
>> But this is not mentioned in the docs.
For engine-specific optimizations, like ICP, yes. Both 5.5 and 5.3 are built and run with XtraDB by default, just as you mentioned, so it doesn't seem to be a very strict requirement, but you are right, it should be mentioned in the docs, we'll fix that.
>> Also the docs mention that MariaDB 5.5 by default uses XtraDB, so I do not expect mtr to be running with InnoDB by default!
MTR configuration is not even remotely close to default MariaDB (or MySQL, for that matter), and it has never been guaranteed, so you shouldn't rely on that. Apart from the engine, it has dozens of non-default parameters.
MTR is just a test tool, with its own specific functionality and configurations, and --start-and-exit is a side road even comparing to that, it's not designed to start a real-life server.
>> As for my comment in bug #1000051, I can see that why ICP is not being used with MariaDB 5.5.23, because mtr would by
>> default disable builtin InnoDB (which is actually XtraDB) and use the InnoDB plugin instead (unexpected behaviour).
>> However, the test case does not force MySQL 5.6.6-m9 to use ICP as well.
Please note that the full test case in bug #1000051 uses MyISAM, not InnoDB, as a table engine. If you create your own tables with a different engine and then attempt to use the test data and query to trigger the same execution plan as provided in the test case, it might well not work. The test case contains explicit ENGINE clauses exactly because the behavior can be engine-specific.
Launchpad bug id: 1052523
Table data to use with the table structure in the bug report
LPexportBug1052523_icp_test.dump