[MDEV-758] LP:1052523 - Unexpected index condition pushdown behaviour on range queries with InnoDB plugin Created: 2012-09-18  Updated: 2012-12-25  Resolved: 2012-12-25

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.5.29

Type: Bug Priority: Minor
Reporter: Ovais Tariq Assignee: Elena Stepanova
Resolution: Won't Fix Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug1052523.xml     File LPexportBug1052523_icp_test.dump    

 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)

 Comments   
Comment by Ovais Tariq [ 2012-09-18 ]

Re: Unexpected index condition pushdown behaviour on range queries

Comment by Ovais Tariq [ 2012-09-18 ]

Table data to use with the table structure in the bug report
LPexportBug1052523_icp_test.dump

Comment by Elena Stepanova [ 2012-09-18 ]

Re: Unexpected index condition pushdown behaviour on range queries
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)
Records: 20976 Duplicates: 0 Warnings: 0

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

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

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

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

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

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

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

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

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

Query OK, 20607 rows affected (1.52 sec)
Records: 20607 Duplicates: 0 Warnings: 0

Query OK, 20565 rows affected (2.88 sec)
Records: 20565 Duplicates: 0 Warnings: 0

Query OK, 20563 rows affected (2.95 sec)
Records: 20563 Duplicates: 0 Warnings: 0

Query OK, 20563 rows affected (1.42 sec)
Records: 20563 Duplicates: 0 Warnings: 0

Query OK, 20565 rows affected (3.37 sec)
Records: 20565 Duplicates: 0 Warnings: 0

Query OK, 20563 rows affected (1.84 sec)
Records: 20563 Duplicates: 0 Warnings: 0

Query OK, 20564 rows affected (4.93 sec)
Records: 20564 Duplicates: 0 Warnings: 0

Query OK, 20562 rows affected (1.66 sec)
Records: 20562 Duplicates: 0 Warnings: 0

Query OK, 20566 rows affected (1.69 sec)
Records: 20566 Duplicates: 0 Warnings: 0

Query OK, 20564 rows affected (4.25 sec)
Records: 20564 Duplicates: 0 Warnings: 0

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

Query OK, 20564 rows affected (4.36 sec)
Records: 20564 Duplicates: 0 Warnings: 0

Query OK, 20564 rows affected (2.15 sec)
Records: 20564 Duplicates: 0 Warnings: 0

Query OK, 20563 rows affected (1.32 sec)
Records: 20563 Duplicates: 0 Warnings: 0

Query OK, 20563 rows affected (5.50 sec)
Records: 20563 Duplicates: 0 Warnings: 0

Query OK, 4472 rows affected (0.87 sec)
Records: 4472 Duplicates: 0 Warnings: 0

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)

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: 223490
                                                        Extra: Using index condition
                                                        1 row in set (0.11 sec)

MariaDB [test]> select @@version;
----------------

@@version

----------------

5.5.27-MariaDB

----------------
1 row in set (0.00 sec)

Comment by Ovais Tariq [ 2012-09-19 ]

Re: Unexpected index condition pushdown behaviour on range queries
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.

Comment by Elena Stepanova [ 2012-09-19 ]

Re: Unexpected index condition pushdown behaviour on range queries
Hi,

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

Comment by Ovais Tariq [ 2012-09-19 ]

Re: Unexpected index condition pushdown behaviour on range queries
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

Comment by Elena Stepanova [ 2012-09-19 ]

Re: Unexpected index condition pushdown behaviour on range queries
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?

Comment by Elena Stepanova [ 2012-09-19 ]

Re: Unexpected index condition pushdown behaviour on range queries
A smaller test case suitable both for MTR and regular server (the original one can also be used):

  1. Either run in MTR as ./mtr <testname>,
  2. or start server with all default parameters to see the result for XtraDB,
  3. 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;

  1. End of test case
  1. 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

--------------------------------------------------------------------------------+

  1. 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

----------------------------------------------------------------------+

Comment by Elena Stepanova [ 2012-09-19 ]

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.

Comment by Ovais Tariq [ 2012-09-20 ]

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!

Comment by Ovais Tariq [ 2012-09-20 ]

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.

Comment by Rasmus Johansson (Inactive) [ 2012-09-20 ]

Launchpad bug id: 1052523

Comment by Elena Stepanova [ 2012-09-20 ]

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.

Generated at Thu Feb 08 06:31:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.