Query optimizations

If the partitioning expression and the partition type have been carefully selected, most queries will only involve one partition or a set of partitions.

In many cases, the optimizer will find out which partitions are not relevant for the current query. This optimization is called partition pruning.

Also, the user can use a SQL clause to specify the list of partitions that must be used. This is called partition selecting.

Note

However, in MariaDB, queries are never parallelized. Even if the optimizer knows that two partitions must be read, and those partitions are on different disks, the same thread will read them sequentially. In particular, one can expect full table scans and full index scans on partitioned tables to run much faster because of parallelization, but this is not the case.

Partition pruning

In MariaDB, partition pruning is possible with RANGE and LIST partitioning, but not with RANGE COLUMNS or LIST COLUMNS. When a statement references the columns used by the...