What is the best index for this query?

[localhost]

Hello guys.

I have this query:

SELECT id
FROM articles
WHERE
DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 1 day) <= dataup
AND
clustcat=1
AND
rate > 1.1
ORDER BY
id DESC

Now this is how my table looks like:

id: mediumint(9)

dataup: timestamp(14)

clustcat: mediumint(9)

rate: decimal(5,3)

I'm trying to make the above query execute faster, the best result i have obtained
using EXPLAIN SELECT is by using 2 indexes:

One index with the date (dataup) and the other one with
clustcat and rate

When using an index with all three of them (dataup,clustcat,rate) i do not get better results, can someone explain this?

Do you think there is a better solution to make the query faster?

Thanks a lot for your attention 🙂

[bastien]

is there a primary key?

Note that mysql determines the best index to use (and it only uses one index per query). If your concatenated query would return more than roughly 30% of the rows, then mysql might not use it as it was no specific enough.