create table ... select, insert into ... select not working
I have a fairly large query that joins 3 columnstore tables. The query executes fine on its own in around 2 minutes. However, when I use the same query for a "create table ... select" or "insert into ... select" then the statements never finish (at least not in the last 3 hours). No errors are reported in the log files that I can see. Any ideas?
Answer Answered by Roman Nozdrin in this comment.
Greetings,
There are three operation modes for SELECTs in ColumnStore. One is the fastest, another we call it disabled vtable could be much slower. More information is here[1]. Complex SELECT queries used in INSERT..SELECT could be run in disabled vtable mode thus their execution time is unpredictable. There is a workaround this. You should use mcsmysql pipelining its output to cpimport. Something like this:
mcsmysql -e 'complex select query' | cpimport
CS will process the query in vtable mode then, not disabled vtable.
PS I would suggest to ask questions in the dedicated Google Group b/c you could get the answer faster.
1. https://mariadb.com/kb/en/library/columnstore-operating-mode/
