I have a MariaDB SQL query (actually very big) using chaining WITH statements like this :
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:
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.