Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.11, 10.9.4
-
Linux, Ubuntu 20.04, n.a.
Description
Customer came up with a query which is linearly slower with the amount of UNIONs attached to a CTE. This should not because CTE should only be evaluated once. He further showed that implementing it with a TEMPORARY TABLE did not show the effect.
Query + Benchmark + Query Execution Plan:
WITH t AS ( |
SELECT SUM(id) AS c, data AS pn |
FROM test |
WHERE ts BETWEEN '2022-11-22 00:00:00' AND '2022-11-22 23:59:59' |
AND data LIKE 'ab%' |
GROUP BY data |
ORDER BY data |
-- MariaDB 10.9: 0.37s
|
-- Competing product: 0.70s
|
-- MariaDB 10.6: 0.66s
|
)
|
SELECT 'AAA' AS label, ((SELECT c FROM t WHERE pn = 'ab06d9ca0e5eb27e5bb2e5c0f345b782')+(SELECT c FROM t WHERE pn = 'ab0f8e861348995d1cbf9ec21d4bf52d'))/((SELECT c FROM t WHERE pn = 'ab134910f6808802cf44b587fb825ab5')+(SELECT c FROM t WHERE pn = 'ab1d6682feb41a97054e8c634ecd5552'))*100 AS value |
-- MariaDB 10.9: 0.79s
|
-- Competing product: 0.70s
|
-- MariaDB 10.6: 2.04s
|
UNION
|
SELECT 'BBB' AS label, (SELECT c FROM t WHERE pn = 'ab2516b576d7463f106b5671bdac6d32')/((SELECT c FROM t WHERE pn = 'ab28e05f4fa736f4738a2ff6cf494f04')+(SELECT c FROM t WHERE pn = 'ab31ab6a7d52fee49990d63dee58371b'))*100 AS value |
-- MariaDB 10.9: 1.38s
|
-- Competing product: 0.70s
|
-- MariaDB 10.6: 3.54s
|
UNION
|
SELECT 'CCC' AS label, (SELECT c FROM t WHERE pn = 'ab390b211d7bb3cc19fc1f808fb1bc3a')/(SELECT c FROM t WHERE pn = 'ab45af9e4ca10d3a618d40ec3f230349')*100 AS value |
-- MariaDB 10.9: 1.76s
|
-- Competing product: 0.70s
|
-- MariaDB 10.6: 4.58s
|
UNION
|
SELECT 'DDD' AS label, ((SELECT c FROM t WHERE pn = 'ab531756624d8287d38eb5fe5809bdb4')+(SELECT c FROM t WHERE pn = 'ab5aaa230cc64c15a61f908aea1c3f9c'))/((SELECT c FROM t WHERE pn = 'ab60f7712cd176b6ac26040d69be0e11')+(SELECT c FROM t WHERE pn = 'ab674e464997f4916e7ed23942c53d23'))*100 AS value |
UNION
|
SELECT 'EEE' AS label, (SELECT c FROM t WHERE pn = 'ab6cc3781441a08d67eb8039521c63bb')/((SELECT c FROM t WHERE pn = 'ab7612bca85db4f6c4638a658fbee646')+(SELECT c FROM t WHERE pn = 'ab7811882f520c2051c6761edf4c306f'))*100 AS value |
UNION
|
SELECT 'FFF' AS label, (SELECT c FROM t WHERE pn = 'ab7f0bd3758fbdac0ff6abb24db166ad')/(SELECT c FROM t WHERE pn = 'ab87d7d598ad47ca5db3bf9e6cbdee48')*100 AS value |
-- MariaDB 10.9: 3.51s
|
-- Competing product: 0.71s
|
-- MariaDB 10.6: 9.15s
|
;
|
|
|
-- MariaDB 10.6 and 10.9 (same QEP)
|
+------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 6 | SUBQUERY | <derived28> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 28 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 5 | SUBQUERY | <derived27> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 27 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 4 | SUBQUERY | <derived26> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 26 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 3 | SUBQUERY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 2 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 7 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 10 | SUBQUERY | <derived31> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 31 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 9 | SUBQUERY | <derived30> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 30 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 8 | SUBQUERY | <derived29> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 29 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 11 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 13 | SUBQUERY | <derived33> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 33 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 12 | SUBQUERY | <derived32> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 32 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 14 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 18 | SUBQUERY | <derived37> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 37 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 17 | SUBQUERY | <derived36> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 36 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 16 | SUBQUERY | <derived35> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 35 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 15 | SUBQUERY | <derived34> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 34 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 19 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 22 | SUBQUERY | <derived40> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 40 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 21 | SUBQUERY | <derived39> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 39 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 20 | SUBQUERY | <derived38> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 38 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 23 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 25 | SUBQUERY | <derived42> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 42 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 24 | SUBQUERY | <derived41> | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| 41 | DERIVED | test | ALL | NULL | NULL | NULL | NULL | 1044500 | Using where | |
| NULL | UNION RESULT | <union1,7,11,14,19,23> | ALL | NULL | NULL | NULL | NULL | NULL | | |
+------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+ |
– Competitive product
+----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+ |
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 8 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 6 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 4 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 2 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 3 | DERIVED | test | NULL | ALL | NULL | NULL | NULL | NULL | 949500 | 1.23 | Using where; Using temporary; Using filesort | |
| 10 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 15 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 13 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 11 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 17 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 20 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 18 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 22 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 29 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 27 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 25 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 23 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 31 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 36 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 34 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 32 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 38 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | |
| 41 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| 39 | SUBQUERY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 131 | const | 10 | 100.00 | NULL | |
| NULL | UNION RESULT | <union1,10,17,22,31,38> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | |
+----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+ |
Testdata can be attached but exceeds size (25.83 MB).
Attachments
Issue Links
- duplicates
-
-
- Open
-
- is part of
-
MDEV-28906 MySQL 8.0 desired compatibility
-
- Open
-
- relates to
-
-
- Open
-