Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
5.5.40
-
Windows Server 2008 R2 64 bit.
-
10.1.8-1
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.