We have a large table (~300m rows, 25GB) containing rating data that has a TIMESTAMP column (on update CURRENT_TIMESTAMP) with an index on that column that allows us to look up and delete data by time. However, when looking things up in that index, we can see that data is missing. We also have an index on all the rows in the table, where lookups work correctly.
CREATE TABLE rating ( rater INT(10) unsigned NOT NULL DEFAULT '0', rated INT(10) unsigned NOT NULL DEFAULT '0', rating SMALLINT(2) unsigned NOT NULL DEFAULT '0', rating_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (rater, rated), KEY rating_timestamp (rating_timestamp), KEY rating (rated, rater, rating, rating_timestamp) ) ENGINE=InnoDB;
rater: 1000 rated: 1001 rating: 1 rating_timestamp: 2012-10-28 01:01:12
Index on all columns (rating: rater, rated, rating, timestamp):
SELECT * FROM rating FORCE INDEX (rating) WHERE rated = 1000 AND rater = 1001 AND rating = 1 AND rating_timestamp = "2012-10-28 01:01:12"; // 1 row returned
Brings back the example row above, whereas the exact same query forcing the timestamp index instead returns 0 rows:
Index on timestamp column (rating_timestamp: rating_timestamp):
SELECT * FROM rating FORCE INDEX (rating_timestamp) WHERE rated = 1000 AND rater = 1001 AND rating = 1 AND rating_timestamp = "2012-10-28 01:01:12"; // 0 rows returned
Is this likely to be corruption in the index on the TIMESTAMP column, or is there some caveat to those type of columns where we can’t look up data in them for some reason? (The dates in question here are old, the earliest data currently in the table).