doesn't partition pruning work if I have range size larger than number of partitions?

243 views Asked by At


I've 15 million of rows in my table and data comes on every 4 second basis. So, I have decided to make partitions on each day as follows

   ALTER TABLE vehicle_gps
   PARTITION BY RANGE(UNIX_TIMESTAMP(gps_time)) (
   PARTITION p01 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')),
                       .
                       .
                       .
   PARTITION p365 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')));

I had to make 365 partitions as shown. Each partitioned day contains data around 100 thousand rows.
And if I want to fetch the data by giving a query

   SELECT gps_time FROM vehicle_gps 
   WHERE gps_time BETWEEN '2014-05-01 00:00:00' AND '2014-05-06 00:00:00';

I found that Partitioning pruning not happening. MySQL manual says if Values in between range are larger than number of partitions, Pruning won't happen. If so then what is the need of creating partitions with tables which contain huge data as mine. Since I'm new to partitioning I'm confused, please guide me if I'm wrong, help me in learning.

Thank You :)

1

There are 1 answers

0
Sergio Ayestarán On

It just doesn't work with dates, small extract from the MySQL Documentation

Pruning can be used only on integer columns of tables partitioned by HASH or KEY. For example, this query cannot use pruning because dob is a DATE column:

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';
However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned.

Hope it helps!