Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
Description
The following query:
SELECT `col_varchar_64_key` FROM `table100000_myisam_int_autoinc`
WHERE ( `col_varchar_64_key` NOT IN ( 'now' , 'rsgxnnowvz' ) OR `col_varchar_64_key` LIKE CONCAT ('Utah' , '%' ) )
AND ( `col_varchar_10_key` BETWEEN 'cr' AND 'really' OR `col_varchar_64_key` IN ( 'j' , 'rcrsgxnn' ) )
AND ( ( `col_varchar_10_key` != 'it' ) OR `col_varchar_10_key` IS NULL )
ORDER BY `col_varchar_64_key` LIMIT 7;
Takes no time on maria-5.1 but takes over 1 second on maria-5.1-wl24 . Even if the filesort is avoided by removing the ORDER BY, considerable performance difference remains.
EXPLAIN under maria-5.1:
id: 1
select_type: SIMPLE
table: table100000_myisam_int_autoinc
type: range
possible_keys: col_varchar_10_key,col_varchar_64_key
key: col_varchar_64_key
key_len: 67
ref: NULL
rows: 99905
Extra: Using where
EXPLAIN under maria-5.1-wl24
id: 1
select_type: SIMPLE
table: table100000_myisam_int_autoinc
type: index_merge
possible_keys: col_varchar_10_key,col_varchar_64_key
key: col_varchar_10_key,col_varchar_64_key
key_len: 13,67
ref: NULL
rows: 54298
Extra: Using sort_union(col_varchar_10_key,col_varchar_64_key); Using where; Using filesort
The table contains 100K rows and will be uploaded shortly.