Pruning partitions in MySQL

Pruning is the selective extraction of data. As we have multiple partitions of big data, it will go through each partition during retrieval, which is time consuming and impacts performance. Some of the partitions will also be included in search while the requested data is not available inside that partition, which is an overhead process. Pruning helps here to search for only those partitions that have the relevant data, which will avoid the unnecessary inclusion of those partitions during retrieval.

This optimization that avoids the scanning of partitions where there can be no matching values is known as the pruning of partitions. In partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions, and scans those database partitions that are relevant to the SQL statement. Let's see an example.

Suppose that we have a table with the following structure:

 CREATE TABLE student (
 rollNo INT NOT NULL,
 name VARCHAR(50...