Advanced MySQL 8

Partition pruning

One of the best-known optimizations is partition pruning. The concept is relatively simple and is based on the principle: "Do not analyze partitions where there can be no corresponding values". Let's assume you have a partitioned called employees, table defined by the following statement:

Consider an example where you want to get the results of a SELECT command such as the following:

SELECT firstname, lastname, job_id, resto_idFROM employeesWHERE job_id > 14 AND job_id < 17;

We need to look only in partitions p2 and p3 to find the corresponding lines.

In other words, partitioning with PRUNE can only be used with a WHERE