Comment 2 for bug 1052523
Revision history for this message
| Elena Stepanova (elenst) wrote Re: Unexpected index condition pushdown behaviour on range queries | #2 |
© 2004
Canonical Ltd.
•
Terms of use
•
Data privacy
•
Contact Launchpad Support
•
Blog
•
Careers
•
System status
•
1b1ed1a
(Get the code!)
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
*******
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)