[SQL] MariaDB: How to using "INSERT ... SELECT" with WITH statement

Note: This involves ColumnStore.

At work, we have a big SQL statement that takes too much memory to execute on prod. I'm currently working on reducing the size the query consumes. I've tried using different approaches, but nothing has solved the issue so far, except for WITH ... AS (...), for some reason. However, I need to combine this with an INSERT INTO ....

This is the code I'm trying to get working

TRUNCATE db1.myTable;

INSERT INTO db1.myTable(`all`, `needed`, `columns`)
(WITH everything AS (
SELECT all, needed, columns
FROM db1.mainTable T1
JOIN db1.secondTable T2
ON (T1.someCol = T2.someCol)
JOIN db2.thirdTable T3
ON (T1.anotherCol = T3.anotherCol)
LEFT JOIN db1.fourthTable T4
ON (T4.anotherCol = T1.anotherCol)
WHERE T2.yetAnotherCol >= (some_SELECT_subquery)
AND T1.valid = 1
) SELECT * FROM everything);


EXPLAIN (WITH everything AS ... returns

+------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 16000000000000 | |
| 2 | PRIMARY | T1 | ALL | NULL | NULL | NULL | NULL | 2000 | Using where with pushed condition |
| 2 | PRIMARY | T2 | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
| 2 | PRIMARY | T3 | ALL | NULL | NULL | NULL | NULL | 2000 | Using where; Using join buffer (flat, BNL join) |
| 2 | PRIMARY | T4 | ALL | NULL | NULL | NULL | NULL | 2000 | Using where |
| 3 | SUBQUERY | some_SELECT_subquery | ALL | NULL | NULL | NULL | NULL | 2000 | Using where with pushed condition |
+------+-------------+-----------------------+------+---------------+------+---------+------+------+-------------------------------------------------+
5 rows in set (0,21 sec)


If I only use the WITH-statement, I can get it to work. As in, I don't use the INSERT INTO. No issues at all, and the query is even faster this way. I also did I quick test with trying to divide the query into several WITHs, but gave up since I believe I messed up the syntax. I'm not too good with SQL, and even less so with JOINs(junior developer).

When I combine the WITH-statement with an INSER INTO ..., MariaDB responds with ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') SELECT * FROM everything)' at line 1. I've also tried adding a semicolon after ... valid = 1, merging the two last lines, positioning the open parentheses after ... AS on a new line, and some other issues I could think of that might be syntax related. No luck.

My current thought is that you can't combine INSERT INTO ... SELECT ... with a WITH .... At least not having the WITH at the beginning, where the SELECT should be. This is what I can gather from the docs.

So, in short, my question is: can I combine INSERT INTO ... SELECT with a WITH-statement at all? If not, can I achieve something similar with another technique?

Are there any other ways I can improve the memory utilization of my query? I'd rather not mess with configuration options for MariaDB or Docker, but if that's the only possibility, I'll consider it.

Continue reading...