(I asked this question in "stack overflow" and was recommended to ask in here).
(MySQL version: 5.6.15)
I have a huge table (Table_A) with 10M rows, in entity-attribute-value model. It has a compound unique key [Field_A + Element + DataTime].
CREATE TABLE TABLE_A ( `Field_A` varchar(5) NOT NULL, `Element` varchar(5) NOT NULL, `DataTime` datetime NOT NULL, `Value` decimal(10,2) DEFAULT NULL, UNIQUE KEY `A_ELE_TIME` (`Field_A`,`Element`,`DataTime`), KEY `DATATIME` (`DataTime`), KEY `ELEID` (`ELEID`), KEY `ELE_TIME` (`ELEID`,`DataTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Rows are inserted/updated to the table every minutes, hence the row size of each [DataTime] (i.e. every minute) is regular, around 3K rows.
I have a "select" query from this table, after the above "inserted/updated". The query selects one specified elements within most recent 25 hours (around 30K rows). This query usually processes within 3 sec.
SELECT Field_A, Element, DataTime, `Value` FROM Table_A WHERE Element="XX" AND DataTime between [time] and [time].
The original housekeeping would be remove any row after 3 days, every 5 minutes.
For better housekeeping, I try to partition the table base on [DataTime], every 6 hours. (00,06,12,18 local time)
PARTITION BY RANGE (TO_DAYS(DataTime)*100+hour(DataTime)) (PARTITION p2014103112 VALUES LESS THAN (73590212) ENGINE = InnoDB, ... PARTITION p2014110506 VALUES LESS THAN (73590706) ENGINE = InnoDB, PARTITION pFuture VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
My housekeeping script will drop the expired partition then create a new one
ALTER TABLE TABLE_A REORGANIZE PARTITION pFuture INTO ( PARTITION [new_partition_name] VALUES LESS THAN ([bound_value]), PARTITION pFuture VALUES LESS THAN MAXVALUE )
The new process seems running smoothly.
However, the SELECT query would slow down suddenly (> 100 sec).
The query is still slow even all process stopped. It won't be fixed until "analyzing partitions" (reads and stores the key distributions for partitions).
It usually happens ones a day.
It does not happen to a non-partitioned table.
Therefore, we think it is caused by corrupted indexing in a partitioned MySQL (huge) table.
Does anyone have any idea on how to solve it?