Details
Major
Description
Issue described here: http://stackoverflow.com/questions/26393529/why-does-mariadb-behave-differentyly-when-an-arbitrarily-high-limit-is-placed-on
A limit of 2^64-1 on a subquery changes the optimization for the better, even though it theoretically has no effect on the subquery.
explain select `quotes`.`dnum`, `quotes`.`rev`, `quotes`.`QuoteName` FROM (select * from `worknet`.`quotes` where `quotes`.`deleted` IS NULL ORDER BY `quotes`.`dateModified` DESC limit 100) as `quotes` LEFT JOIN (SELECT CAST(`dnum` as UNSIGNED) as `anum` from `worknet`.`nsos`) AS `sosStatusDb` ON `quotes`.`dnum`=`sosStatusDB`.`anum` where `quotes`.`deleted` IS NULL ORDER BY `quotes`.`dateModified` DESC LIMIT 100
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | nsos | index | NULL | PRIMARY | 18 | NULL | 62243 | Using where; Using index; Using join buffer (flat, BNL join) |
| 2 | DERIVED | quotes | index | NULL | date_modified | 5 | NULL | 100 | Using where |
Changing the subquery to the following results in a different result
(SELECT CAST(`dnum` as UNSIGNED) as `anum` from `worknet`.`sos` LIMIT 18446744073709551615) AS `sosStatusDb`
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using filesort |
| 1 | PRIMARY | <derived3> | ref | key0 | key0 | 5 | quotes.dnum | 622 | |
| 3 | DERIVED | nsos | index | NULL | PRIMARY | 18 | NULL | 62243 | Using index |
| 2 | DERIVED | quotes | index | NULL | date_modified | 5 | NULL | 100 | Using where |
The query time changes from 9.1 seconds to .078.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I updated the queries above to have a more watered down query, and to include a cast statement. Without the cast statement, the explain still changes, but the performance doesn't change as I originally reported. So this should more accurately describe the situation.
'nsos', 'CREATE TABLE `nsos` ( `dnum` int(11) NOT NULL DEFAULT \'1\', `dateModified` int(10) unsigned NOT NULL, PRIMARY KEY (`dnum`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC'
'quotes', 'CREATE TABLE `quotes` ( `dnum` int(10) unsigned NOT NULL, `rev` int(11) unsigned NOT NULL AUTO_INCREMENT, `dateModified` int(11) DEFAULT NULL, `SalesID` varchar(50) DEFAULT NULL, `userDnum` int(11) DEFAULT NULL, `QuoteName` varchar(99) DEFAULT NULL, `Customer` varchar(60) DEFAULT NULL, `accountId` int(11) DEFAULT NULL, `Product` text, `Price` float(16,2) DEFAULT NULL, `importedPrice` float(16,2) DEFAULT NULL , `currency` varchar(3) DEFAULT NULL , `Status` varchar(20) DEFAULT NULL, `safetyStandard` varchar(45) DEFAULT NULL, `Comments` text, `BidDate` int(11) DEFAULT NULL, `BidDateApptId` varchar(255) DEFAULT NULL, `DelegateBidDateApptId` varchar(255) DEFAULT NULL, `accessLog` longtext, `deleted` varchar(45) DEFAULT NULL, `deletedDate` int(11) DEFAULT NULL, `lockedBy` varchar(45) DEFAULT NULL, `template` varchar(45) DEFAULT NULL, `additionalcomments` text, `bayAreaJob` varchar(45) DEFAULT NULL, `requestedBy` varchar(99) DEFAULT NULL, `estimator` varchar(99) DEFAULT NULL, `estimatorDnum` int(11) DEFAULT NULL, `requestedDate` varchar(99) DEFAULT NULL, `so` text, `origin` int(11) DEFAULT NULL , `attn` varchar(255) DEFAULT NULL , `drawingLeadTime` varchar(255) DEFAULT NULL, `shipLeadTime` varchar(255) DEFAULT NULL, `location` text, `region` int(11) DEFAULT \'0\' , `rvp` text, `rep` text, `repDnum` int(11) DEFAULT NULL, `highChance` text, `importedFromExcel` bit(1) DEFAULT NULL, `importedFileTimestamp` int(11) DEFAULT NULL, `importedTime` int(11) DEFAULT NULL, `bidComplete` int(11) DEFAULT NULL , `fromRev` int(11) DEFAULT \'-1\' , `clonedFromRev` int(11) DEFAULT \'-1\' , `type` int(11) DEFAULT NULL , `suppliers` varchar(255) DEFAULT NULL, PRIMARY KEY (`dnum`,`rev`), KEY `salesID-origin-deleted` (`SalesID`,`origin`,`deleted`), KEY `date_modified` (`dateModified`) USING BTREE, KEY `quoteNum` (`dnum`) ) ENGINE=MyISAM AUTO_INCREMENT=45388 DEFAULT CHARSET=utf8 MAX_ROWS=100000'
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| nsos | 0 | PRIMARY | 1 | dnum | A | 60753 | NULL | NULL | BTREE |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| quotes | 0 | PRIMARY | 1 | dnum | A | NULL | NULL | NULL | BTREE | |||
| quotes | 0 | PRIMARY | 2 | rev | A | 34365 | NULL | NULL | BTREE | |||
| quotes | 1 | salesID-origin-deleted | 1 | SalesID | A | 1909 | NULL | NULL | YES | BTREE | ||
| quotes | 1 | salesID-origin-deleted | 2 | origin | A | 34365 | NULL | NULL | YES | BTREE | ||
| quotes | 1 | salesID-origin-deleted | 3 | deleted | A | 34365 | NULL | NULL | YES | BTREE | ||
| quotes | 1 | date_modified | 1 | dateModified | A | 34365 | NULL | NULL | YES | BTREE | ||
| quotes | 1 | quoteNum | 1 | dnum | A | 34365 | NULL | NULL | BTREE |
Thank you.
Assigning to Sergei Petrunia to evaluate how much of a bug it is and what it takes to fix it.
Even if it's a bug, I don't expect it to be fixed in 5.5, but ultimately it's Sergei Petrunia 's decision, maybe it's something simple and basic.
As far as I can see limit just prevent merging and in this case materialization is more efficient.
I attached dump of database where I was experimenting.
In discussion with Sergey Petrunia we figured our that in case of merge expression CAST(`dnum` as UNSIGNED) prevent using hash join. In case of materialization we already has 2 fields and hash join works.
At the moment optimizer this situation is beyond cases it can detect. So only manual optimization can help. Sorry.
Hi,
Could you please send SHOW CREATE TABLE and SHOW INDEX IN output for quotes, sostatus and sos?
I tried to create the tables based on queries, but I'm getting different plans (also changing upon adding LIMIT, but still different from yours).
Thanks.