Unexpected index condition pushdown behaviour on range queries with InnoDB plugin

[Ovais Tariq]

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)

[Elena Stepanova (elenst)]

Hi,

I'm getting ICP with your test case (tried 5.5.27 which is the latest 5.5 release):

MariaDB [test]> 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;
Query OK, 0 rows affected (0.12 sec)

MariaDB [test]> SOURCE icp_test.dump;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 21539 rows affected (0.93 sec)
Records: 21539 Duplicates: 0 Warnings: 0

Query OK, 21405 rows affected (0.89 sec)
Records: 21405 Duplicates: 0 Warnings: 0

Query OK, 21405 rows affected (1.27 sec)
Records: 21405 Duplicates: 0 Warnings: 0

Query OK, 21000 rows affected (0.91 sec)
Records: 21000 Duplicates: 0 Warnings: 0

Query OK, 20977 rows affected (1.40 sec)
Records: 20977 Duplicates: 0 Warnings: 0

Query OK, 20975 rows affected (1.22 sec)
Records: 20975 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (1.21 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (0.94 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20974 rows affected (1.23 sec)
Records: 20974 Duplicates: 0 Warnings: 0

Query OK, 20975 rows affected (1.89 sec)
Records: 20975 Duplicates: 0 Warnings: 0

Query OK, 20975 rows affected (1.68 sec)
Records: 20975 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (1.22 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20977 rows affected (1.39 sec)
Records: 20977 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (1.54 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20977 rows affected (1.74 sec)
Records: 20977 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (0.99 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20977 rows affected (1.34 sec)
Records: 20977 Duplicates: 0 Warnings: 0

Query OK, 20978 rows affected (1.99 sec)
Records: 20978 Duplicates: 0 Warnings: 0

Query OK, 20975 rows affected (2.26 sec)
Records: 20975 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (1.35 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20975 rows affected (2.18 sec)
Records: 20975 Duplicates: 0 Warnings: 0

Query OK, 20979 rows affected (1.67 sec)
Records: 20979 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (1.80 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20977 rows affected (1.27 sec)
Records: 20977 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (1.94 sec)
Records: 20976 Duplicates: 0 Warnings: 0

Query OK, 20976 rows affected (2.09 sec)
Record...

[Ovais Tariq (ovais-tariq)]

Elena,

I am able to reproduce this with 5.5.23 and 5.5.27. Following is how the optimizer_switch values look like:

MariaDB [test]> select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

You can see index_condition_pushdown=on

Every time I run this query, the EXPLAIN shows its not using ICP. However, the same query run any number of times on MySQL 5.6.6-m9 and 5.3.5 uses ICP. So for me its unpredictable when the optimizer would choose to use ICP.

[Elena Stepanova (elenst)]

Hi,

Please attach your complete cnf file (with all includes, if it has any). I'll check if it makes any difference.

[Ovais Tariq (ovais-tariq)]

I am using mtr to start a test instance, so the cnf is the one that is auto-generated. I am starting up the test instance as follows:
./mtr --start-and-exit

[Elena Stepanova (elenst)]

Okay, thanks, it makes a difference. So, you're actually using InnoDB plugin, not XtraDB. Was it also true for your comment in bug #1000051?

[Elena Stepanova (elenst)]

A smaller test case suitable both for MTR and regular server (the original one can also be used):

# Either run in MTR as ./mtr <testname>,
# or start server with all default parameters to see the result for XtraDB,
# and with --ignore-builtin-innodb --innodb --plugin-load=ha_innodb.so to see the result for InnoDB plugin

--source include/have_innodb.inc

CREATE TABLE t1 (i INT NOT NULL, k INT NOT NULL, v CHAR(32), KEY k(k,i)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,3227,'f90dd1afb12d8078da97215431ccaf6e'), (2,7286,'fe2a44fd61aec8c76d71cd2fd5cd0a16');

EXPLAIN SELECT * FROM t1 WHERE k BETWEEN 4000 AND 5000 AND i < 1000 ORDER BY k ASC LIMIT 10;

DROP TABLE t1;

# End of test case

# Result for XtraDB:

+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| 1 | SIMPLE | t1 | range | k | k | 8 | NULL | 1 | Using index condition |
+------+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

# Result for InnoDB plugin:

+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | k | k | 8 | NULL | 1 | Using where |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+

[Elena Stepanova (elenst)]

(assignment was a mistake)
ICP is not implemented in InnoDB plugin 5.5, so not surprisingly it doesn't show up in the explain.

[Ovais Tariq (ovais-tariq)]

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!

[Ovais Tariq (ovais-tariq)]

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.

[Elena Stepanova (elenst)]

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.