stack stack - 1 year ago 65
MySQL Question

Why if I add one more condition on WHERE clause, then using an index fails?

Here is my query:

SELECT count(1)
FROM qanda question
JOIN qanda answer ON question.Id = answer.related
WHERE answer.related IS NOT NULL
AND answer.author_id = 29
AND question.amount IS NULL
AND not in (
select post_id
from votes
where table_code = 15
group by post_id
having sum(value) < 0)

And this is the
result of it:

enter image description here

As you see, all tables are using an index. Ok, now I need to add one more condition on the
clause of outer query. This is that condition:

... AND from_unixtime(answer.date_time) BETWEEN (now() - INTERVAL 1 year) AND (now() - INTERVAL 1 hour)

Well, after adding that new condition, this is the
result of it:

enter image description here

table doesn't use any index anymore. Why? And what index do I need to make query above faster and more efficient?

Answer Source

"Using index" implies that the index is "covering". That is, all the columns (from that table) that are needed for the query are in that composite index. It appears that answer has INDEX(author_id, related, id). Hence, the best approach is to use the index BTree and ignore the Data BTree.

When you added a condition on date_time, and that column was not part of the same index, the query might be run one of these ways:

  • Look in the index's BTree, then reach over to the data to check the date_time. Repeat.
  • Ignore the index and simply scan the data.

The cutoff between the two choices varies with the phase of the moon, but typically is somewhere around 20%. The EXPLAIN implies that it chose the latter approach. (See Drew's excellent analogy!)

Change the index to INDEX(author_id, date_time, related, id) (in that order) and change the new where clause to


This index will still be "covering", so it should continue to be "Using index". The order of columns is: (1) check for equality, (2) check a range, then whatever other columns are needed for 'covering'.

This index will be not quite as good for the original query, but it will still be 'covering'.

The change to the WHERE is to avoid "hiding" the column date_time inside a function, which prevents the use of an index.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download