Getting unexpected result with Range Partitioning in MySQL

Question

I'm trying to partitioning using Range for all days in 2014

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')));

If I insert few lesser rows It's partitioning as expected. sitting in particular partition and that's fine.

But when I try to insert thousands of rows at a time, for instance values which supposed to sit on 2014-07-07 00:00:00 placing at last partition i.e p365.

Another Problem Which I have been facing is...when I type following Query again and again after inserting values into partitions

      SELECT partition_name, table_rows 
      FROM information_schema.partitions
      WHERE table_name = 'vehicle_gps';

The number of rows in each partition changing

why??? Please help me in solving these problems

Thank You :)

Rick James Rick James · Accepted Answer · 2015-06-14T21:29:02

InnoDB Row counts are approximate. This probably explains "The number of rows in each partition changing". Treat the number as being (usually) within a factor of 2 of the exact number of rows.

For performance reasons, don't have more than about 50 partitions in a table.

As for why the batch insert stored into the wrong partition, I would like to see more 'proof'. Perhaps some pattern will arise from some examples of such.

Getting unexpected result with Range Partitioning in MySQL

1 Answers