Description:
There is a problem with mysql query running either non-determistically or deterministically badly on 4.1.x and 5.0.27.
The query is:
SELECT `c9`, `c10`, `c8`, `c11`, `c12`, `c13`,
IF(`c2` IS NULL, 0, 1) + IF(`c1` IS NULL, 0, 2) AS a1,
IF(`c5` IS NULL, 0, 1) + IF(`c3` IS NULL, 0, 1) +
IF(`c7` IS NULL, 0, 1) + IF(`c4` IS NULL, 0, 1) AS a2
FROM `t1`
WHERE (((`c2` = '4551') OR (`c2` IS NULL))
AND ((`c1` = 'something') OR (`c1` IS NULL))
AND ((`c5` = 'someother') OR (`c5` IS NULL))
AND ((`c3` = 'US') OR (`c3` IS NULL))
AND ((`c7` = 'somethingelse') OR (`c7` IS NULL))
AND ((`c4` = '') OR (`c4` IS NULL)))
ORDER BY a1 DESC, a2 DESC, c8, c11, c12;
Explain from 5.1.14:
+----+-------------+----------------+-------+---------------+-------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+-------+---------+------+------+-----------------------------+
| 1 | SIMPLE | t1 | range | k1 | k1 | 294 | NULL | 248 | Using where; Using filesort |
+----+-------------+----------------+-------+---------------+-------+---------+------+------+-----------------------------+
This runs in about .02 seconds.
5.0.27 / 4.1.x gives:
+----+-------------+----------------+-------------+-----------------------+-------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------------+-----------------------+-------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | t1 | ref_or_null | k1 | k1 | 33 | const | 8 | Using where; Using filesort |
+----+-------------+----------------+-------------+-----------------------+-------+---------+-------+------+-----------------------------+
This runs in .12 sec. Occasionally it runs using "range" and is faster, but this is the minority case.
How to repeat:
CREATE TABLE `t1` (
`c1` varchar(255) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` char(2) DEFAULT NULL,
`c4` varchar(255) DEFAULT NULL,
`c5` varchar(32) DEFAULT NULL,
`c6` char(2) DEFAULT NULL,
`c7` varchar(255) DEFAULT NULL,
`c8` float(6,2) unsigned NOT NULL DEFAULT '100.00',
`c9` varchar(255) DEFAULT NULL,
`c10` varchar(255) DEFAULT NULL,
`c11` int(11) DEFAULT NULL,
`c12` int(11) DEFAULT NULL,
`c13` varchar(255) DEFAULT NULL,
KEY `k1` (`c1`(30),`c2`,`c5`(16),`c6`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Put about 28M of rows in it. Check EXPLAIN results and runtime for the query above, with query cache disabled.
Make small change in data and run ANALYZE table, check EXPLAIN again. Many times. In 5.0.27 and 4.1.x slow, "ref_or_null", methods is used in most(!) cases. On 5.1.14 faster "range" access method is constantly used.
