DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE `t1` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`v1` varchar(3),
|
`v2` varchar(3),
|
`v3` int,
|
PRIMARY KEY (`id`),
|
KEY `idx` (`v1`,`v2`)
|
) ENGINE=MyISAM;
|
|
-- fill table with 1024 rows for a start
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
INSERT INTO t1 SELECT NULL, 'abc', 'xyz' , 0 FROM t1;
|
|
-- now add 1024 rows each using different patterns
|
INSERT INTO t1 SELECT NULL, 'abc', '' , 0 FROM t1 LIMIT 1024;
|
INSERT INTO t1 SELECT NULL, '' , 'xyz' , 0 FROM t1 LIMIT 1024;
|
INSERT INTO t1 SELECT NULL, '' , '' , 0 FROM t1 LIMIT 1024;
|
|
-- reading directly from the index returns 1024, as expected
|
SELECT COUNT(*) FROM t1 WHERE v1 = ' ' AND v2 <> ' ';
|
|
-- but when adding the extra colum it switches to index condition pushdown
|
-- and returns zero instead of 1024
|
SELECT COUNT(*) FROM t1 WHERE v1 = ' ' AND v2 <> ' ' AND v3 = 0;
|
|
-- forcing the index to be ignored: 1024 rows reported correctly
|
SELECT COUNT(*) FROM t1 IGNORE INDEX(idx) WHERE v3=0 AND v1 = ' ' AND v2 <> ' ';
|
|
-- switching off index_condition_pushdown results in 1024 rows
|
SET optimizer_switch='index_condition_pushdown=off';
|
SELECT COUNT(*) FROM t1 WHERE v3=0 AND v1 = ' ' AND v2 <> ' ';
|
|
-- the problem is also specific to MyISAM only
|
-- InnoDB returns 1024 just fine, even though index condition
|
-- pushdown is used
|
SET optimizer_switch='index_condition_pushdown=on';
|
ALTER TABLE t1 ENGINE=InnoDB;
|
SELECT COUNT(*) FROM t1 WHERE v3=0 AND v1 = ' ' AND v2 <> ' ';
|
|
-- Aria handles this correctly, too
|
ALTER TABLE t1 ENGINE=Aria;
|
SELECT COUNT(*) FROM t1 WHERE v3=0 AND v1 = ' ' AND v2 <> ' ';
|