Here is my query:
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 answer.id not in (
where table_code = 15
group by post_id
having sum(value) < 0)
... AND from_unixtime(answer.date_time) BETWEEN (now() - INTERVAL 1 year) AND (now() - INTERVAL 1 hour)
"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
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:
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
AND datetime >= UNIX_TIMESTAMP(now() - INTERVAL 1 YEAR) AND datetime < UNIX_TIMESTAMP(now() - INTERVAL 1 HOUR)
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.