Why does this MySQL query take 25+ seconds? The query is:

SELECT T.ID, T.A, T.T 
FROM  table1 AS T
WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much 
Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T


I have a BTREE index over 2 columns - userID, and another column which doesn't feature in the above query. The index is set up with userID being the first column.

The table is innoDB, and the total table size is around 70 million, but the rows that userID='333335' equals total only about 500,000.

If userID has an index on it, and that's the first column queried, shouldn't the rest of the query be able to breeze through 500,000 rows pretty quickly?

What is it that I'm overlooking please?

Thank you for your time and help.
Your index may be finding the records relatively quickly, but it has to perform a double lookup for every single one of those rows that it finds for that userId.

Try adding the following index:

(userId, A, T, category)


to table1.

This will be a covering index, and should yield extremely quick results.