We are considering using MemSQL to provide screening capabilities against our data. A screening operation can have an arbitrary number of criteria -- the type of requirement that would most likely be addressed using "Dynamic SQL" in Oracle or MySQL.
Would this type of arbitrary screening be a problem for MemSQL? This FAQ from MemSQL suggests that compiling a query takes time. Does it mean that each time MemSQL sees a new WHERE clause, it will have to compile a new C++ fragment? If so, how long of a delay would that compilation introduce?
Thanks.
That is correct, as described in the FAQ each new WHERE clause will require query compilation, unless the only difference is the values of parameters. (This is because we compile and optimize the plan based on the filters available in the where clause.) So if you add a new filter, that would require another compilation. In the current release of memsql, compilation for a select query may take ~1s or more, depending on the query.
One workaround to avoid this, if you know all possible criteria you're interested in beforehand, is to write a WHERE clause like:
WHERE (a=1 OR 0) AND (b<5 OR 0) AND (c=3 OR 0)
which is equivalent to
WHERE (a=1) AND (b<5) AND (c=3)
Of course, those filters can be arbitrary expressions. By changing an OR 0 to OR 1, you ignore that condition, so e.g. to get just b<5 you write
WHERE (a=1 OR 1) AND (b<5 OR 0) AND (c=3 OR 1)
This doesn't require additional compilation, because only the parameters are changing. Note however that it may not perform as well because the compiled query plan has to check each of those clauses at runtime (that's how this workaround avoids recompilation) and the best plan may be different depending on which filters are actually present.