We are hitting a wrong ordering in 5.2.10 but it does not happen on a
test box using 5.3 series (tried 5.3.3 and 5.3.5).
The query can be rewritten and when we do, the range becomes an index
scan and produces the correct results.
Here are the queries:
[Incorrect Order]
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE StockNo LIKE
'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo LIKE 'JG%'
ORDER BY $number DESC LIMIT 1;
EXPLAIN Result
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE inv range PRIMARY PRIMARY 66 (NULL)
4 Using where;
Using index; Using filesort
[Correct Order]
SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM inv WHERE
LEFT(StockNo,2) IN('JR','VS','JB','JG')
ORDER BY $number DESC LIMIT 1;
*EXPLAIN Result*
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE inv index (NULL) PRIMARY 66 (NULL) 2496
Using where;
Using index; Using filesort
Simplified Table Structure:
CREATE TABLE `tableinventory` (
`StockNo` varchar(64) NOT NULL DEFAULT '',
`ItemDesc` varchar(96) NOT NULL DEFAULT '',
PRIMARY KEY (`StockNo`)
) ENGINE=PBXT DEFAULT CHARSET=latin1;
All data is using a length of 7 even though it is defined as varchar(64).
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
