maria-discuss team mailing list archive

[Brian Evans <grknight@xxxxxxxxxxxxxx>]

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).