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 :)
It just doesn't work with dates, small extract from the MySQL Documentation
Hope it helps!