# Logical Transformation - Partition Pruning Example
```
tidb> CREATE TABLE User (
 -> fname VARCHAR(50) NOT NULL,
 -> lname VARCHAR(50) NOT NULL,
 -> city VARCHAR(50) NOT NULL,
 -> login DATE NOT NULL
 -> )
 -> PARTITION BY RANGE COLUMNS (login)
 -> (
 -> PARTITION `p0` VALUES LESS THAN ('2023-01-01 00:00:00'),
 -> ~!@PARTITION `p1` VALUES LESS THAN ('2024-01-01 00:00:00')@!~,
 -> PARTITION `p2` VALUES LESS THAN ('2025-01-01 00:00:00')
 -> );

tidb> SELECT * FROM User 
 -> WHERE city IN ('Seattle', 'Tokyo') 
 -> ~!@AND login = '2023-09-01'@!~;
```
![`smart`](PartitionPruning_aa_002_20230922.png)
------------------------------------------------------------------------------
For partitioned tables, we can utilize partition pruning optimization. The core idea of partition pruning is to avoid scanning partitions that do not match the query criteria.

As illustrated on the slide, if the result falls into partition `p1`, then you only need to search for the matching rows within `p1`. Excluding unnecessary partitions is what we refer to as partition pruning. When the optimizer successfully prunes some partitions, the query execution in a partitioned table can be significantly faster than in a non-partitioned table.

The optimizer can skip partitions based on conditions in `WHERE` clauses, including but not limited to the following scenarios:  
- For all partition types: `<partition_column> = <constant>`  
- For all partition types: `<partition_column> IN (<constant1>, <constant2>, ..., <constantN>)`  
- For `RANGE` partition types: `<partition_column> BETWEEN <constant1> AND <constant2>`  

Note that partition pruning does not work with `LIKE` conditions. For more detailed cases where partition pruning is applicable, see: [https://docs.pingcap.com/tidb/stable/partitioned-table#some-cases-for-partition-pruning-to-take-effect](https://docs.pingcap.com/tidb/stable/partitioned-table#some-cases-for-partition-pruning-to-take-effect).