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 :)
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.