CODEWITHSUNDEEP
mysqlmyisam
clipworks
clipworks

Reputation: 23

MySQL query slow because of separate indexes?

Here is my situation. I have a MySQL MyISAM table containing about 4 million records with a total of 13,3 GB of data. The table contains messages received from an external system. Two of the columns in the table keep track of a timestamp and a boolean whether the message is handled or not.

When using this query:

SELECT MIN(timestampCB) FROM webshop_cb_onx_message

The result shows up almost instantly.

However, I need to find the earliest timestamp of unhandled messages, like this:

SELECT MIN(timestampCB ) FROM webshop_cb_onx_message WHERE handled = 0

The results of this query show up after about 3 minutes, which is way too slow for the script I'm writing.

Both columns are individually indexed, not together. However, adding an index to the table would take incredibly long considering the amount of data that is in there already.

Does my problem originate from the fact that both columns are separatly indexed, and if so, does anyone have a solution to my issue other than adding another index?

Upvotes: 2

Views: 152

Answers (1)

Steve
Steve

Reputation: 3703

It is commonly recommended that if the selectivity of an index over 20% then a full table scan is preferable over an index access. This would mean it is likely that your index on handled won't actually result in using the index but a full table scan given the selectivity.

A composite index of handled, timestampCB may actually improve the performance given its a composite index, even if the selectivity isn't great MySQL would most likely still use it - even if it didn't you could force it's use.

Upvotes: 2

Related Questions