I don't know what effect REPAIR TABLE would have on PBXT. But worth a try I think.
-- Peter On Fri, Mar 16, 2012 at 17:30, Brian Evans <[email protected]> wrote: > After testing inserting the full data into a new table, it seems the > fault is in PBXT somewhere. > This is a very old table that is constantly accessed. I can only > reproduce it in the existing table. > Any attempt to create it in a new one fails to have the same results. > > Also, data appears in the range version that seems to be transactional > that is rolled back and not in the table by the indexes. > > This is a bad sign for PBXT. > > Brian > > > On 3/16/2012 11:53 AM, Peter Laursen wrote: > > I think specific data are required to reproduce wrong ordering. I cannot > reproduce with a few inserted data. I do like this: > > SELECT VERSION() #5.2.10-MariaDB > > DROP TABLE IF EXISTS `tableinventory`; > > CREATE TABLE `tableinventory` ( > `StockNo` VARCHAR(64) NOT NULL DEFAULT '', > `ItemDesc` VARCHAR(96) NOT NULL DEFAULT '', > PRIMARY KEY (`StockNo`) > ) ENGINE=PBXT DEFAULT CHARSET=latin1; > > INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES > ('JBccccccc','ccccccc'); > INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES > ('JGddddddd','ddddddd'); > INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES > ('JRaaaaaaa','aaaaaaa'); > INSERT INTO `tableinventory`(`StockNo`,`ItemDesc`) VALUES > ('VSbbbbbbb','bbbbbbb'); > > SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE > LEFT(StockNo,2) IN('JR','VS','JB','JG') ORDER BY $number DESC; > > SELECT SUBSTRING(StockNo, 3, 3) AS $number FROM `tableinventory` WHERE > StockNo LIKE 'JR%' OR StockNo LIKE 'VS%' OR StockNo LIKE 'JB%' OR StockNo > LIKE 'JG%'ORDER BY $number DESC; > > /* and both SELECTs return the same expected ordering om my environment > (Win7/64 - MariaDB 5.2.10): > > $number > --------- > ddd > ccc > bbb > aaa > */ > > Maybe characters used in 'StockNo' are a little less trivial than 'a', > 'b' etc. in the environment where you see the problem? > > > Peter > (not a MP person) > > > On Fri, Mar 16, 2012 at 16:01, Brian Evans <[email protected]>wrote: > >> 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 : [email protected] >> Unsubscribe : https://launchpad.net/~maria-discuss >> More help : https://help.launchpad.net/ListHelp >> >> > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
