Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3.29, 10.5.11
-
None
-
Linux Debian 10 or 11, dedicated server or local test machine
Description
I have a MariaDB SQL query (actually very big) using chaining WITH statements like this :
WITH tbl_base AS (
|
|
|
-- Base level sub-query on a table containing about 22000 rows, using LEFT JOIN on 5 other tables, details not relevant here |
SELECT ……… FROM <many things>
|
|
|
), tbl_middle AS (
|
|
|
-- Intermediate level sub-query #1 for intermediate computing, details not relevant here |
SELECT ……… FROM tbl_middle …
|
|
|
), tbl_states AS (
|
|
|
-- Intermediate level sub-query #2 for intermediate computing, details not relevant here |
SELECT
|
……… AS state,
|
……… AS `elec`
|
FROM tbl_states …
|
|
|
), tbl_sums AS (
|
|
|
-- Final grouping query
|
SELECT
|
`state`,
|
SUM(NOT `elec`) AS `vls`,
|
SUM(`elec`) AS `vae`,
|
count(`state`) AS `all`
|
FROM tbl_states
|
GROUP BY `state`
|
ORDER BY `state`
|
|
|
)
|
|
|
-- Additional query which could be avoided if left as simple as this but it will not stay as simple as this |
SELECT `state`, `vae`, `vls`, `both` FROM tbl_sums
|
This query makes statistics on two kinds of objects which can each have 8 different states (bad, longrun, lost, maint, ok, removed, run or warn), so the result is a tiny view of only 8 rows and 4 columns (state, vls, vae and both).
On my server it needs about 500 mS to execute, which is acceptable.
Now I want to add rows for creating some combined states, so I replaced the last SELECT by this UNION:
SELECT `state`, `vae`, `vls`, `both` FROM tbl_sums
|
UNION
|
SELECT 'all_sta',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums WHERE `state` IN ('ok','warn','bad','maint') |
UNION
|
SELECT 'all_run',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums WHERE `state` IN ('run','longrun') |
UNION
|
SELECT 'operative',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums WHERE `state` IN ('ok','warn','run','longrun') |
UNION
|
SELECT 'unusable',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums WHERE `state` IN ('bad','maint') |
UNION
|
SELECT 'present',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums WHERE `state` IN ('ok','warn','bad','maint','run','longrun') |
UNION
|
SELECT 'missing',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums WHERE `state` IN ('removed','lost') |
UNION
|
SELECT 'total',SUM(`vls`),SUM(`vae`),SUM(`both`) FROM tbl_sums |
I works fine, but I am very surprised to observe that the query needs now about 4 seconds to run, thus 8 times the initial 500 mS, likely because I am reusing the last WITH element 8 times.
Apparently MariaDB execute this by computing 8 times the whole sub-query chain instead of temporarily storing the last one and reusing it 8 times, whereas I was expecting it to be able to reuse the last tiny result in not even a millisecond.
If I use EXPLAIN on the last query, I get a result like this (see joined picture) where we could clearly see that the whole sub-queries are effectively explored 8 times.
So I'd like to know if I made something bad, or if I should change some settings in order to have it working better, or if this is simply a bug in MariaDB (versions 10.3.29 or 10.5.11 in this case), as WITH is a quite new feature in MariaDB.
I know I could create a temporary table, but as it is very complicated to execute multiple statements for ONE result in my application, I was precisely expecting to use WITH in order to avoid it.
This is likely irrelevant, but the server is running on Linux Debian 11.
Also, I do that using MyISAM tables. Not tried with others.
Attachments
Issue Links
- is duplicated by
-
-
- Open
-
- is part of
-
MDEV-28906 MySQL 8.0 desired compatibility
-
- Open
-
- relates to
-
-
- Open
-