| Bug #59870 | Slow VIEWS by using UNION | ||
|---|---|---|---|
| Submitted: | 1 Feb 2011 13:40 | Modified: | 3 Feb 2011 14:51 |
| Reporter: | Richard Teubel | Email Updates: | |
| Status: | Verified | Impact on me: | |
| 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 | ||
| Triage: | Triaged: D2 (Serious) | ||
[1 Feb 2011 13:41]
Richard Teubel
how to repeat
Attachment: how_to_repeat.txt (text/plain), 10.46 KiB.
[1 Feb 2011 13:44]
Richard Teubel
SQL-File without my outputs
Attachment: how_to_repeat.sql (application/octet-stream, text), 4.16 KiB.
[1 Feb 2011 14:24]
Valeriy Kravchuk
Looks like a duplicate of bug #59819. Please, check.
[1 Feb 2011 14:26]
Miguel Solorzano
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)
[2 Feb 2011 13:36]
Richard Teubel
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?
[3 Feb 2011 14:51]
Valeriy Kravchuk
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)
[11 May 2011 15:44]
Jannes Faber
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).
[26 Oct 2016 4:52]
Sreeharsha Ramanavarapu
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.
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