MySQL Bugs: #59870: Slow VIEWS by using UNION

Bug #59870	Slow VIEWS by using UNION
Submitted:	1 Feb 2011 13:40	Modified:	11 Aug 2020 19:49
Reporter:	Richard Teubel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S5 (Performance)
Version:	5.0.89, 5.1.56, 5.5.8, 5.5.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	slow, UNION, VIEW

Description:
Views with UNION are inefficient and slow. They pass through all data sets by using where statement. All following queries have for this demo a sleep function in select to simulate other function calls.

A Query without UNION in VIEW:

mysql> SELECT *
    -> FROM v_View_WithoutHeadline
    -> WHERE myGroup = 1
    -> LIMIT 5;
+----+---------+------------------+------------+
| ID | myGroup | myContent        | SLEEP(0.1) |
+----+---------+------------------+------------+
|  4 |       1 | :aOFtA           |          0 |
|  5 |       1 | PVq@:8.          |          0 |
|  8 |       1 | q                |          0 |
|  9 |       1 | DWANGTF&         |          0 |
| 10 |       1 | PO&0ABH_gW1K`S a |          0 |
+----+---------+------------------+------------+
5 rows in set (0.50 sec) <<---- quick!

A Query with UNION in VIEW:
Show at execution time!!

mysql> SELECT *
    -> FROM v_View_WithHeadline
    -> WHERE myGroup = 1
    -> LIMIT 5;
+------+---------+------------+-----------+
| ID   | myGroup | myContent  | Sleeptime |
+------+---------+------------+-----------+
| NULL |       1 | Headline 1 |         0 |
|    4 |       1 | :aOFtA     |         0 |
|    5 |       1 | PVq@:8.    |         0 |
|    8 |       1 | q          |         0 |
|    9 |       1 | DWANGTF&   |         0 |
+------+---------+------------+-----------+
5 rows in set (10.11 sec) <<---- slow!

The Query from VIEW with UNION:

mysql> select
    ->     NULL AS `ID`,
    ->     `myHeadline`.`myGroup` AS `myGroup`,
    ->     `myHeadline`.`Headline` AS `myContent`,
    ->     0 AS `Sleeptime`
    ->   from
    ->     `myHeadline`
    ->   WHERE myGroup = 1
    ->
    ->     union
    ->
    ->   select
    ->     `myTable`.`ID` AS `ID`,
    ->     `myTable`.`myGroup` AS `myGroup`,
    ->     `myTable`.`myContent` AS `myContent`,
    ->     sleep(0.1) AS `Sleeptime`
    ->   from
    ->     `myTable`
    ->   WHERE myGroup = 1
    ->   LIMIT 5;
+------+---------+------------+-----------+
| ID   | myGroup | myContent  | Sleeptime |
+------+---------+------------+-----------+
| NULL |       1 | Headline 1 |         0 |
|    4 |       1 | :aOFtA     |         0 |
|    5 |       1 | PVq@:8.    |         0 |
|    8 |       1 | q          |         0 |
|    9 |       1 | DWANGTF&   |         0 |
+------+---------+------------+-----------+
5 rows in set (0.51 sec) <<----- quick!

The execution time is constant if I change algorithm of view.

How to repeat:
look additional file

how to repeat

Attachment: how_to_repeat.txt (text/plain), 10.46 KiB.

SQL-File without my outputs

Attachment: how_to_repeat.sql (application/octet-stream, text), 4.16 KiB.

Looks like a duplicate of bug #59819. Please, check.

On Windows VIsta 64-bit:

mysql 5.5 >SELECT *
    -> FROM v_View_WithoutHeadline
    -> WHERE myGroup = 1;
+----+---------+---------------------+------------+
| ID | myGroup | myContent           | SLEEP(0.1) |
+----+---------+---------------------+------------+
|  4 |       1 | :aOFtA              |          0 |
|  5 |       1 | PVq@:8.             |          0 |
|  8 |       1 | q                   |          0 |
<cut>
| 87 |       1 | Yl                  |          0 |
| 88 |       1 | KauU{rA'            |          0 |
| 95 |       1 | +t9UclCEzdpYiduH#3? |          0 |
+----+---------+---------------------+------------+
34 rows in set (3.40 sec)

mysql 5.5 >SELECT *
    -> FROM v_View_WithHeadline
    -> WHERE myGroup = 1;
+------+---------+---------------------+-----------+
| ID   | myGroup | myContent           | Sleeptime |
+------+---------+---------------------+-----------+
| NULL |       1 | Headline 1          |         0 |
|    4 |       1 | :aOFtA              |         0 |
|    5 |       1 | PVq@:8.             |         0 |
|    8 |       1 | q                   |         0 |
<cut>
|   87 |       1 | Yl                  |         0 |
|   88 |       1 | KauU{rA'            |         0 |
|   95 |       1 | +t9UclCEzdpYiduH#3? |         0 |
+------+---------+---------------------+-----------+
35 rows in set (10.00 sec)

mysql 5.5 >
mysql 5.5 >select
    -> NULL AS `ID`,
    -> `myHeadline`.`myGroup` AS `myGroup`,
    -> `myHeadline`.`Headline` AS `myContent`,
    -> 0 AS `Sleeptime`
    -> from
    -> `myHeadline`
    -> WHERE myGroup = 1
    ->
    -> union
    ->
    -> select
    -> `myTable`.`ID` AS `ID`,
    -> `myTable`.`myGroup` AS `myGroup`,
    -> `myTable`.`myContent` AS `myContent`,
    -> sleep(0.1) AS `Sleeptime`
    -> from
    -> `myTable`
    -> WHERE myGroup = 1;
+------+---------+---------------------+-----------+
| ID   | myGroup | myContent           | Sleeptime |
+------+---------+---------------------+-----------+
| NULL |       1 | Headline 1          |         0 |
|    4 |       1 | :aOFtA              |         0 |
|    5 |       1 | PVq@:8.             |         0 |
<cut>
|   88 |       1 | KauU{rA'            |         0 |
|   95 |       1 | +t9UclCEzdpYiduH#3? |         0 |
+------+---------+---------------------+-----------+
35 rows in set (3.40 sec)

mysql 5.5 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.1.5               |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.10              |
| version_comment         | Source distribution |
| version_compile_machine | x86                 |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)

Hello Valeriy,

i think it's not a duplicate of bug #59819 because:
- i use in my Query only one table, without any joins
- i use WHERE conditions
- queries at row table and views without WHERE conditions have the same execution time. In the other bug the execution time is different.
- my view query is only slow, if i use UNION. Internal optimizer isn't efficient 

What you think is similarly at the bugs?

Verified also with 5.1.56 on Linux. This is what happens according to EXPLAIN:

mysql> explain select     NULL AS `ID`,     `myHeadline`.`myGroup` AS `myGroup`,     `myHeadline`.`Headline` AS `myContent`,     0 AS `Sleeptime`     from     `myHeadline`     WHERE myGroup = 1               union             select          `myTable`.`ID` AS `ID`,          `myTable`.`myGroup` AS `myGroup`,          `myTable`.`myContent` AS `myContent`,          sleep(0.1) AS `Sleeptime`        from          `myTable`        WHERE myGroup = 1        LIMIT 5;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | PRIMARY      | myHeadline | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |
|  2 | UNION        | myTable    | ref   | Group         | Group   | 5       | const |   18 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |             |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)

Compare to the case with view:

mysql> explain select * from v_View_WithHeadline where myGroup=1 LIMIT 5;
+----+--------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  110 | Using where |
|  2 | DERIVED      | myHeadline | ALL  | NULL          | NULL | NULL    | NULL |   10 |             |
|  3 | UNION        | myTable    | ALL  | NULL          | NULL | NULL    | NULL |  100 |             |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+----+--------------+------------+------+---------------+------+---------+------+------+-------------+
4 rows in set (10.16 sec)

mysql> explain select * from v_View_WithoutHeadline where myGroup=1 LIMIT 5;
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key   | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
|  1 | SIMPLE      | myTable | ref  | Group         | Group | 5       | const |   18 | Using where |
+----+-------------+---------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)

I keep running into the same problem. MySQL doesn't push down clauses into the UNIONed queries, even in really simple cases.

This bug is possibly a duplicate of: #36802  http://bugs.mysql.com/bug.php?id=36802

That bug also shows an exception case where clauses do get pushed down (basically all fields should be in an index).

Posted by developer:
 
Consider the following queries:

Query1:
-------
SELECT *
FROM t1
WHERE c1 = 1

UNION

SELECT *
FROM t2
WHERE c1 = 1;

Query2:
-------
CREATE VIEW v1 AS
SELECT *
FROM t1

UNION

SELECT *
FROM t2;

SELECT *
FROM view1
WHERE c1 = 1;

Query1 is faster and has less handler calls because the conditions in the WHERE clause are pushed down.

Query2 is slower and has more handler calls because Optimizer doesn't push the condition in the WHERE for views.

Condition pushdown for views/derived tables is an feature request and will need to be done as part of WL#8084.

This bug will be deferred.

Fixed in MySQL 8.0.22 by WL#8084.

See same for docs info.

Closed.