Not the answer you need?
Register and ask your own question!

Index on TIMESTAMP column corrupt or missing information

WheresWardyWheresWardy ContributorInactive User Role Beginner
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.

Table structure:
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;

Example row:
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).
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.