Code GenerationΒΆ
The first time a query is executed against MemSQL its execution will be slower than subsequent executions. The reason for this slow down is that the first time a particular query shape is seen by MemSQL, the query will be compiled and cached in memory. Code generation involves pulling all the constants out of the query and replacing them with parameters and then turning the resulting parametrized query into a C++ program and compiling it into a shared object (.so file). Subsequent executions of the same parametrized query will re-use this shared object to execute. Code generation also occurs for CREATE TABLE statements.
Running the following queries against MemSQL demonstrates this effect:
memsql> SELECT * FROM t WHERE col = 1;
Empty set (1.42 sec)
memsql> SELECT * FROM t WHERE col = 1;
Empty set (0.00 sec)
memsql> SELECT * FROM t WHERE col = 100000;
Empty set (0.00 sec)
The first query took a long time to run even though it returned no rows. The second and third queries execute quickly as they re-use the shared object compiled for the first query. The important thing to note is that even though the constant in the WHERE clause changed in the third execution of the query the same shared object was still used. This demonstrates how parametrizing constants allows for greater re-use of shared objects.
To see how many parametrized queries MemSQL has compiled plans for run SHOW PLANCACHE. This command shows all the parametrized queries along with some execution statistics for each query.
The compiled shared objects for each query and table are stored on disk in the memsqlbin/plancache/<database name> directory. These shared objects will be reused if MemSQL is restarted to avoid the cost of re-compiling a query already compiled by a previous execution of MemSQL.