Bug #83152 Badly optimized query with VIEWs (regression)
Submitted: 26 Sep 2016 15:03 Modified: 27 Oct 2016 5:24
Reporter: Vojtech Kurka Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.7.14 OS:Red Hat (CentOS 7)
Assigned to: CPU Architecture:Any
Tags: view views merge optimizer index

[26 Sep 2016 15:03] Vojtech Kurka 
Description:
When using MERGE VIEW in a query with a subquery, MySQL 5.7 uses full table scan instead of an index.
I have a query that works perfectly on MySQL 5.6, but performs very badly on 5.7 - after an in-place upgrade.

How to repeat:
I have two databases:
1.) "kg" - schema with a real table
2.) "kg_view" - schema with a view of the original table from "kg"

The original table is fast:

mysql> use kg
Database changed
mysql> SET @a = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `interval`, cnt, ((@a := (@a + tmp1.`cnt`)) ) AS `sum` 
    -> FROM ( 
    ->     SELECT ptai.`interval`, SUM(ptai.`count`) AS cnt 
    ->       FROM PageTimeAggregateInterval ptai 
    ->      WHERE ptai.`time` BETWEEN DATE_SUB('2016-09-26 15:15:54', INTERVAL 5 MINUTE) AND '2016-09-26 15:15:54'
    ->      GROUP BY ptai.`interval`
    ->      ORDER BY ptai.`interval`
    ->   ) AS tmp1
    -> ORDER BY tmp1.`interval` ;
+----------+------+------+
| interval | cnt  | sum  |
+----------+------+------+
|       10 |   30 |   30 |
|       15 |  216 |  246 |
|       20 |  234 |  480 |
...
|      940 |    1 | 4125 |
|      950 |    2 | 4127 |
|     1100 |    1 | 4128 |
|     1400 |    2 | 4130 |
|     2900 |    1 | 4131 |
+----------+------+------+
85 rows in set (0.02 sec)

But when using the view, the query is incredibly slower:

mysql> use kg_views
Database changed
mysql> SET @a = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT `interval`, cnt, ((@a := (@a + tmp1.`cnt`)) ) AS `sum` 
    -> FROM ( 
    ->     SELECT ptai.`interval`, SUM(ptai.`count`) AS cnt 
    ->       FROM PageTimeAggregateInterval ptai 
    ->      WHERE ptai.`time` BETWEEN DATE_SUB('2016-09-26 15:15:54', INTERVAL 5 MINUTE) AND '2016-09-26 15:15:54'
    ->      GROUP BY ptai.`interval`
    ->      ORDER BY ptai.`interval`
    ->   ) AS tmp1
    -> ORDER BY tmp1.`interval` ;
+----------+------+------+
| interval | cnt  | sum  |
+----------+------+------+
|       10 |   30 |   30 |
|       15 |  216 |  246 |
|       20 |  234 |  480 |
|       25 |  284 |  764 |
...
|      950 |    2 | 4127 |
|     1100 |    1 | 4128 |
|     1400 |    2 | 4130 |
|     2900 |    1 | 4131 |
+----------+------+------+
85 rows in set (7.61 sec)

mysql> EXPLAIN SELECT `interval`, cnt, ((@a := (@a + tmp1.`cnt`)) ) AS `sum` 
    -> FROM ( 
    ->     SELECT ptai.`interval`, SUM(ptai.`count`) AS cnt 
    ->       FROM PageTimeAggregateInterval ptai 
    ->      WHERE ptai.`time` BETWEEN DATE_SUB('2016-09-26 15:15:54', INTERVAL 5 MINUTE) AND '2016-09-26 15:15:54'
    ->      GROUP BY ptai.`interval`
    ->      ORDER BY ptai.`interval`
    ->   ) AS tmp1
    -> ORDER BY tmp1.`interval` ;
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table                     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                                        |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>                | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  412949 |   100.00 | Using filesort                               |
|  2 | DERIVED     | <derived3>                | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3716916 |    11.11 | Using where; Using temporary; Using filesort |
|  3 | DERIVED     | PageTimeAggregateInterval | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 3716916 |   100.00 | NULL                                         |
+----+-------------+---------------------------+------------+------+---------------+------+---------+------+---------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> use kg
Database changed
mysql> EXPLAIN SELECT `interval`, cnt, ((@a := (@a + tmp1.`cnt`)) ) AS `sum` 
    -> FROM ( 
    ->     SELECT ptai.`interval`, SUM(ptai.`count`) AS cnt 
    ->       FROM PageTimeAggregateInterval ptai 
    ->      WHERE ptai.`time` BETWEEN DATE_SUB('2016-09-26 15:15:54', INTERVAL 5 MINUTE) AND '2016-09-26 15:15:54'
    ->      GROUP BY ptai.`interval`
    ->      ORDER BY ptai.`interval`
    ->   ) AS tmp1
    -> ORDER BY tmp1.`interval` ;
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                  |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL | 1981 |   100.00 | Using filesort                                         |
|  2 | DERIVED     | ptai       | NULL       | range | time          | time | 4       | NULL | 1981 |   100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

View definition:

mysql> show create view kg_views.PageTimeAggregateInterval\G
*************************** 1. row ***************************
                View: PageTimeAggregateInterval
         Create View: CREATE ALGORITHM=MERGE VIEW `kg_views`.`PageTimeAggregateInterval` AS select `kg`.`PageTimeAggregateInterval`.`time` AS `time`,`kg`.`PageTimeAggregateInterval`.`interval` AS `interval`,`kg`.`PageTimeAggregateInterval`.`count` AS `count`,`kg`.`PageTimeAggregateInterval`.`project` AS `project`,`kg`.`PageTimeAggregateInterval`.`server` AS `server` from `kg`.`PageTimeAggregateInterval`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

Here is the EXPLAIN on another DB instance with MySQL 5.6 (same dataset):

mysql> EXPLAIN SELECT `interval`, cnt, ((@a := (@a + tmp1.`cnt`)) ) AS `sum` 
    -> FROM ( 
    ->     SELECT ptai.`interval`, SUM(ptai.`count`) AS cnt 
    ->       FROM PageTimeAggregateInterval ptai 
    ->      WHERE ptai.`time` BETWEEN DATE_SUB('2016-09-26 15:15:54', INTERVAL 5 MINUTE) AND '2016-09-26 15:15:54'
    ->      GROUP BY ptai.`interval`
    ->      ORDER BY ptai.`interval`
    ->   ) AS tmp1
    -> ORDER BY tmp1.`interval` ;
+----+-------------+---------------------------+-------+---------------+------+---------+------+------+--------------------------------------------------------+
| id | select_type | table                     | type  | possible_keys | key  | key_len | ref  | rows | Extra                                                  |
+----+-------------+---------------------------+-------+---------------+------+---------+------+------+--------------------------------------------------------+
|  1 | PRIMARY     | <derived2>                | ALL   | NULL          | NULL | NULL    | NULL | 1980 | Using filesort                                         |
|  2 | DERIVED     | PageTimeAggregateInterval | range | time          | time | 4       | NULL | 1980 | Using index condition; Using temporary; Using filesort |
+----+-------------+---------------------------+-------+---------------+------+---------+------+------+--------------------------------------------------------+
2 rows in set (0.03 sec)
[27 Sep 2016 5:24] MySQL Verification Team 
Hello Vojtech,

Thank you for the report.
In order to reproduce this issue at our end, please could you provide provide exact reproducible test case(schema and data)? If the data you need to attach is more than 3MB, please compress(recommended filename: mysql-bug-data-83152.zip) and upload one to our sftp.oracle.com 
 

Thanks,
Umesh
[28 Oct 2016 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".