SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    nyc
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    indexes on timestamp columns

    First time poster here, but this seems like a pretty great forum.

    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. (I can't use "FORCE INDEX" because I'm running MySQL 4.0.)

    Am I missing something? Is there a reason why MySQL can't use that index?

    Many thanks for any suggestions.

  2. #2
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've had this happen on larger tables after a while (it has nothing to do with the timestamp data type). Running an OPTIMIZE TABLE (or even ANALYZE TABLE) should do the trick. This will update the key distribution statistics for the index. After doing that, run an EXPLAIN and you should see the index being used once more...

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2004
    Location
    nyc
    Posts
    63
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by silent
    I've had this happen on larger tables after a while (it has nothing to do with the timestamp data type). Running an OPTIMIZE TABLE (or even ANALYZE TABLE) should do the trick. This will update the key distribution statistics for the index. After doing that, run an EXPLAIN and you should see the index being used once more...
    Thanks for the reply. I tried running ANALYZE and OPTIMIZE but it didn't appear to have any effect. However, I managed to simplify the query somewhat (it includes a total of 4 tables and specified DISTINCT and an ORDER BY on one of the related tables) to eliminate processing that could more efficiently be done on a script level. After that streamlining, it's using the index as intended.

    Thanks again for your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •