Hi,
I've got a MySQL table that uses a timestamp column, which is indexed. I need to run frequent queries on this table based on rows with a timestamp above a certain value. For some reason, MySQL refuses to use the timestamp index and insists on doing a full table scan every time. It's a large table, so this is quite resource intensive. Since it only needs a very small percentage of the actual records (those which were added within the last 8 hours, typically), it seems like the query would run much faster if it used that timestamp index. Nonetheless, even when specifying that it should do so with "USE INDEX (timestamp_col)" MySQL still does a full table scan.
Am I missing something? Is there a reason why MySQL can't use that index?
Many thanks for any suggestions.
Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
I've got a MySQL table that uses a timestamp column, which is indexed. I need to run frequent queries on this table based on rows with a timestamp above a certain value. For some reason, MySQL refuses to use the timestamp index and insists on doing a full table scan every time. It's a large table, so this is quite resource intensive. Since it only needs a very small percentage of the actual records (those which were added within the last 8 hours, typically), it seems like the query would run much faster if it used that timestamp index. Nonetheless, even when specifying that it should do so with "USE INDEX (timestamp_col)" MySQL still does a full table scan.
Am I missing something? Is there a reason why MySQL can't use that index?
Many thanks for any suggestions.
Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
optimizing timestamp indexes in MySQL