GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Index on TIMESTAMP column corrupt or missing information

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Index on TIMESTAMP column corrupt or missing information

    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:
    Code:
    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:
    Code:
    rater: 1000
    rated: 1001
    rating: 1
    rating_timestamp: 2012-10-28 01:01:12
    Index on all columns (rating: rater, rated, rating, timestamp):
    Code:
    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):
    Code:
    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).
Working...
X