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 answer.id not in (
  select post_id
  from votes
  where table_code = 15
  group by post_id
  having sum(value) < 0)

And this is the EXPLAIN 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 WHERE 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 EXPLAIN result of it:

enter image description here

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


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 (type is ALL and key is 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:

AND answer.date_time 
        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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.