[MDEV-30017] MariaDB optimizer significantly slower with CTE than competitive product - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.6.11, 10.9.4
Fix Version/s: 10.6
Component/s: Optimizer - CTE
Labels:
- CTE
- foundation
- optimizer
- subquery
- union
Environment:
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

MDEV-26591 Is MariaDB really caching or reusing “WITH” intermediary results?

Open

is part of

MDEV-28906 MySQL 8.0 desired compatibility

Open

relates to

MDEV-30312 Improvement: make derived_with_keys not generate identical draft keys

Open

Activity

Ascending order - Click to sort in descending order

Oli Sennhauser created issue - 2022-11-15 16:06

Daniel Black made changes - 2022-11-17 01:44

Field	Original Value	New Value
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).	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: {code:sql} 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 \| \| +------+--------------+------------------------+------+---------------+------+---------+------+---------+----------------+ {code} -- Competitive product {code:sql} +----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+ \| 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 \| +----+--------------+-------------------------+------------+------+---------------+-------------+---------+-------+--------+----------+----------------------------------------------+ {code} Testdata can be attached but exceeds size (25.83 MB).

Daniel Black added a comment - 2022-11-17 01:45

Thanks oli,

Test data welcome to upload on https://mariadb.com/kb/en/meta/mariadb-ftp-server/

Daniel Black added a comment - 2022-11-17 01:45 Thanks oli , Test data welcome to upload on https://mariadb.com/kb/en/meta/mariadb-ftp-server/

Oli Sennhauser added a comment - 2022-11-17 07:31

File uploaded...

Oli Sennhauser added a comment - 2022-11-17 07:31 File uploaded...

Daniel Black added a comment - 2022-11-17 12:38

https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html

Consider the following query:

SELECT *

 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2

         ON t1.f1=derived_t2.f1;

The optimizer constructs an index over column f1 from derived_t2 if doing so would enable use of ref access for the lowest cost execution plan

Daniel Black added a comment - 2022-11-17 12:38 https://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html Consider the following query: SELECT * FROM t1 JOIN ( SELECT DISTINCT f1 FROM t2) AS derived_t2 ON t1.f1=derived_t2.f1; The optimizer constructs an index over column f1 from derived_t2 if doing so would enable use of ref access for the lowest cost execution plan

Daniel Black made changes - 2022-11-17 12:39

Assignee

Michael Widenius [ monty ]

Sergei Golubchik made changes - 2022-11-25 16:31

Assignee

Michael Widenius [ monty ]

Igor Babaev [ igor ]

Sergei Golubchik made changes - 2022-11-25 16:31

Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]

Daniel Black made changes - 2023-01-05 21:24

Link

This issue relates to MDEV-30312 [ MDEV-30312 ]

Vicențiu Ciorbaru made changes - 2023-01-10 08:34

Assignee

Igor Babaev [ igor ]

Vicențiu Ciorbaru [ cvicentiu ]

Julien Fritsch made changes - 2023-03-03 17:36

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 14:58

Fix Version/s

10.8 [ 26121 ]

Daniel Black made changes - 2023-07-06 07:14

Link

This issue duplicates MDEV-26591 [ MDEV-26591 ]

Daniel Black made changes - 2023-07-06 08:05

Link

This issue is part of MDEV-28906 [ MDEV-28906 ]

Julien Fritsch made changes - 2023-11-28 10:39

Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]

Vlad Radu made changes - 2025-02-06 07:20

Labels

CTE optimizer subquery union

CTE foundation optimizer subquery union

People

Assignee:: Vicențiu Ciorbaru

Reporter:: Oli Sennhauser

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2022-11-15 16:06

Updated:: 2025-02-06 07:20

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration