Hello,
We’ve been battling for some time with what is fundamentally a simple query.
We have a databases with about 15 columns which store int and float values. The query searches in each column with an IN condition , where the possible test values in the IN condition may vary from a 1 to 200 - where these 200 conditions are all the possible values of that column.
So normally a query would look something like: SELECT id FROM table WHERE a IN (1,2,3,4) AND b IN (4,5,6,) AND c IN (2,5,6) ...... AND i = 5 etc.
The database has about 2 mil rows but will grow to potentially 10 mil rows.
A query similar to the one above takes about 80-100 ms. With about 50 test values for each IN.
So far, the best results we got with MariaDB and proper indexing using Engine Memory where the above query takes about 2ms.
We suspect MonetDB is not optimised for such queries, but since we don’t know much about its workings can you please confirm this? Otherwise, is there a way to improve the above query? Say using multiple JOINs instead of IN ?
By the way, we are experimenting with MonetDB out of curiosity, we heard a lot of good things about it.
Thank you very much, Silviu