Perhaps you already know, but
Using index confuses a lot of people -- they assume it means the query is using an index, and that's not what it means.
That means the query is using the index as a covering index -- reading row data from the index, ignoring the main table because the index contains all the necessary data, which is generally assumed to be faster since the amount of data to be read should be smaller.
Adding the extra condition -- which is not sargable, but that's a second problem -- defeats the covering index, which is expected, but it also triggers a full table scan (
NULL), which seems counter-intuitive since this can still be resolved with an equality ref against the index and evaluating the condition
Using where... and this is probably because you're testing against a small data set. With more rows, the optimizer might choose a different path.
An index on
(author_id, date_time) would be useful -- and might even switch the query back to
Using index, though it wouldn't be optimal, because -- as I mentioned -- it's not sargable.
Except in very limited circumstances, you don't want to use a column as the argument to a function in the
WHERE clause, because this must be evaluated by scanning or at least filtering.
Assuming answer.date_time is an
INT UNSIGNED column containing the unix epoch time of the event (otherwise using
FROM_UNIXTIME() here is incorrect, anyway) the more desirable expression is this:
BETWEEN UNIX_TIMESTAMP(now() - INTERVAL 1 year)
AND UNIX_TIMESTAMP(now() - INTERVAL 1 hour);
The value of
NOW() is frozen when a query begins executing, no matter how long the query takes to run. Because of this fact, UNIX_TIMESTAMP(NOW() - INTERVAL 1 YEAR) is resolvable to a constant, as is UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR). The optimizer will resolve both of these to integer constants, and then can use an index including the
date_time column to identify the specific rows using a range scan.