Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5(EOL), 10.0(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
-
Ubuntu 12.04, CentOS 5.11
Description
When derived_merge optimization is ON, server optimizes away derived table even in case when resulting join is much slower than materializing derived table and joining to it. On production database it is difference between 0.37s without merge and 11.85s with merge on warmed up caches and buffer pool, but slow log shows even cases with 5-20 minutes.
I prepared a test case with simpler structure (will upload dump). I use group by primary to make subquery not-mergeable - it does not change results in this case.
explain
|
SELECT a.handle, |
Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count`, |
Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount`, |
Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count_month`, |
Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount_month`, |
Sum(IF(collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00',-val,0)) AS `payout_month` |
FROM a AS a |
LEFT JOIN |
(SELECT b.`type`,b.`created`, b.`val`, b.`collected`, b.`a_id` |
FROM b AS b |
WHERE ((created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00') |
OR (created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00') |
OR (collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00')) |
) AS `T` ON T.a_id = a.id |
WHERE (a.handle <> 1000) |
GROUP BY a.handle; |
+------+-------------+-------+-------+------------------------+--------+---------+-------------------------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+------------------------+--------+---------+-------------------------+------+--------------------------+
|
| 1 | SIMPLE | a | range | handle | handle | 5 | NULL | 399 | Using where; Using index |
|
| 1 | SIMPLE | b | ref | a_id,created,collected | a_id | 5 | derived_merge_test.a.id | 365 | Using where |
|
+------+-------------+-------+-------+------------------------+--------+---------+-------------------------+------+--------------------------+
|
RUN:
|
399 rows in set (0.41 sec)
|
explain
|
SELECT a.handle, |
Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count`, |
Sum(IF(TYPE IS NULL AND created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount`, |
Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',1,0)) AS `count_month`, |
Sum(IF(TYPE IS NULL AND created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00',val,0)) AS `amount_month`, |
Sum(IF(collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00',-val,0)) AS `payout_month` |
FROM a AS a |
LEFT JOIN |
(SELECT b.`type`, b.`created`, b.`val`, b.`collected`, b.`a_id` |
FROM b AS b |
WHERE ((created >= '2015-04-07 06:00:00' AND created < '2015-04-08 06:00:00') |
OR (created >= '2015-03-31 22:00:00' AND created < '2015-04-08 06:00:00') |
OR (collected IS NOT NULL AND collected >= '2015-03-31 22:00:00' AND collected < '2015-04-08 06:00:00')) |
GROUP BY id) AS `T` ON T.a_id = a.id |
WHERE (a.handle <> 1000) |
GROUP BY a.handle; |
+------+-------------+------------+-------------+-------------------+-------------------+---------+-------------------------+------+------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+------------+-------------+-------------------+-------------------+---------+-------------------------+------+------------------------------------------------------------------+
|
| 1 | PRIMARY | a | range | handle | handle | 5 | NULL | 399 | Using where; Using index |
|
| 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | derived_merge_test.a.id | 10 | |
|
| 2 | DERIVED | b | index_merge | created,collected | created,collected | 8,9 | NULL | 3918 | Using sort_union(created,collected); Using where; Using filesort |
|
+------+-------------+------------+-------------+-------------------+-------------------+---------+-------------------------+------+------------------------------------------------------------------+
|
RUN:
|
399 rows in set (0.05 sec)
|