Partition pruning is an optimization technique that can be implemented for partitioned tables. Some tables are so large that a full table scan is unthinkable. If you scan the full table to get your data, it will reduce your query performance as well as response time. To overcome this you can use “Partition Pruning” technique to scan only the matched partitions.
The idea behind pruning is relatively simple and it can be described as “Exclude scanning of partition that do not contain any matched value.” Consider the following range partitioned table table1,
CREATE TABLE table1 (
user_name varchar(128) NOT NULL,
company_name varchar(128) NOT NULL,
id bigint(10) unsigned NOT NULL,
)
PARTITION BY RANGE(`id`) (
PARTITION p0 VALUES LESS THAN (50),
PARTITION p1 VALUES LESS THAN (150),
PARTITION p2 VALUES LESS THAN (250),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Here `id` is a partitioned column. Now, consider the case where you wish to obtain results from a query such as:
SELECT user_name, company_name, id FROM table1 WHERE id > 100 AND id < 230;
In this case, we can see that there is no use of checking this query in either of the partitions p0 or p3; We need to search only in partitions p1 and p2 to obtain the matching rows. By doing this optimization, we can reduce the response time by scanning only the matched partitions instead of scanning all partitions in table. This “cutting away” of unneeded partitions is known as “pruning”.
;
The query optimizer will perform pruning whenever a WHERE condition can be reduced to either of the following two cases:
- partition_column = constant
- partition_column IN (constant 1, constant 2, constant 3,…., constant N)
In the first case, the mysql query optimizer simply determines which partition the given value belongs to, and then scans only that partition in that list.
Ex:- SELECT user_name, company_name, id FROM table1 WHERE id=126;
We can also use different arithmetic comparison operators, including <, >, <=, >=, and <> in the place of = operator in the above query.
In the second case, the mysql query optimizer creates a list of matching partitions, and then scans only the matched partitions in that list.
Ex:- SELECT user_name, company_name, id FROM table1 WHERE id in (149,150,249,250);
In this example, the optimizer can determine that the first two values in the WHERE condition belongs to partition p1, the remaining two values found in partition p2, so optimizer only will search in partition p1 and p2. Queries using BETWEEN in the WHERE clause can also take advantage of pruning.
Partition pruning can be applied to SELECT, DELETE and UPDATE statements and INSERT statement cannot be pruned.
Partition pruning reduces the amount of data retrieved from disk and processing time, thus improving query performance, reduces search time and optimizing resource utilization.
Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.
