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

Hi

You might try to use "FORCE INDEX" (MySQL 4.0.9 and higher) instead of "USE INDEX". According to the MySQL documentation, a table scan will then only be used if there is no way to use one of the given indexes to find rows in the table. A table scan is assumed to be very expensive if you're using "FORCE INDEX" (but not if you're using "USE INDEX").

Hope this helps,
Oliver

Thanks Oliver. I should have specified, I'm using MySQL 4.0, so unfortunately I don't have access to FORCE INDEX.

I keep playing around with this, and it really seems like MySQL has trouble with < or > searches on indexed timestamp columns. It just doesn't want to use those indexes. I can't imagine why that would be, but it seems to be universally the case.

Anybody have any ideas for a workaround of some kind??

Thanks again in advance.

---

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund

Can you show the output of:

explain "yours select query"

and "describe table" and "show index from table" to see what's going on?

Cheers,

Alex
--
Gossamer Threads Inc.

Hi Alex,

Thanks for the reply. I've actually managed to get this fixed, although it was a little tricky. Basically, I think the SELECT query was just a bit too complex to use the index properly, since it included four related tables and specified a DISTINCT and ORDER BY from one of the other (i.e. non-timestamped) tables. Since that sort of filtering could all be done on a script level, I removed it from the database query. I also replaced the timestamp column with an int column that collects unix timestamps. Those changes, together, seem to have whipped things into shape, and the EXPLAIN output now shows that the index is being used as intended.

---

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund