Query with simple join and ORDER BY takes thousands times longer when run with ICP
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug Description
Initially reported in the knowledge base: http://
The following query
SELECT SQL_NO_CACHE *
FROM A, B
WHERE b1 = a1
AND a3 = "3"
ORDER BY a2 DESC;
takes much longer when it's run with index_condition
Actual values can vary depending on the machine, but as an example, on my local box on the test data it takes ~ 0.1 sec with ICP=off and 10+ sec with ICP=on.
bzr version-info
revision-id: <email address hidden>
date: 2012-05-15 08:31:07 +0300
revno: 3523
Also reproducible on MariaDB 5.5 (revno 3403) and MySQL trunk (revno 3827).
EXPLAIN:
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE A ref a3,a3_2 a3_2 2 const 2540 100.00 Using index condition; Using where
1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test.A.a1 1 100.00 Using index
Warnings:
Note 1003 select sql_no_cache `test`.`A`.`a1` AS `a1`,`test`
Full optimizer_switch:
index_merge=
# Test case
# please note that the test case requires the data file A.data,
# it is attached
CREATE TABLE A (
a1 INT(6),
a2 DOUBLE,
a3 ENUM('0'
KEY(a3),
KEY(a3,a2)
) ENGINE=MyISAM;
LOAD DATA LOCAL INFILE 'A.data' INTO TABLE A;
CREATE TABLE B (
b1 INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (b1)
) ENGINE=MyISAM;
INSERT INTO B VALUES
(NULL)
INSERT INTO B SELECT NULL FROM B t2a, B t2b, B t2c;
INSERT INTO B SELECT NULL FROM B t2a, B t2b;
DELETE FROM B ORDER BY RAND() LIMIT 14000;
SELECT SQL_NO_CACHE *
FROM A, B
WHERE b1 = a1
AND a3 = "3"
ORDER BY a2 DESC;
# End of test case
Changed in maria: | |
importance: | Undecided → High |
Changed in maria: | |
status: | New → Confirmed |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
The initial user's data can be found on FTP as lp-1000051.tar.gz. It's considerably bigger.