0

I have a table with a date time column. I have a query currently written as:

SELECT *
FROM foo
WHERE is_relevant = true
ORDER BY ABS(TIMESTAMPDIFF(SECOND, `foo`.`date`, NOW()))

The goal is to return the records whose time is closest to right now -- a record 1 minute in the future is closer (lower in order) than one 5 minutes in the past, is closer than 1 hour in the future, and so on.

Problem is, I don't think SQL can manage the index on the above query very well. It's a computed result that has to be computed every time the query is run, and I'm not sure how I can create a cache value for it that will be performant. Is there any way to improve this?

For context, the original query is much more complex, with multiple joins; the table with the timestamp isn't the one being SELECT'd from, but rather a joined table to help filter and order the query, and worse yet has the potential for null values due to a left-join.

1 Answer 1

1

Your query, as presented, will fetch the 'relevant' rows, sort them and then return them. This is not a lot worse than getting the set without sorting them.

If you will be putting a LIMIT on the query, now the difference may be big. Without a suitable index, it still does the step above. On the other hand, if there could be a suitable index, it might have read only the number of rows indicated by the LIMIT.

Continuing with the LIMIT, it is possible to do a speedup without any special index:

( SELECT *
    FROM foo
    WHERE is_relevant = true
      AND date >= NOW()
    ORDER BY foo.date ASC
    LIMIT 10
) UNION ALL (
( SELECT *
    FROM foo
    WHERE is_relevant = true
      AND date < NOW()
    ORDER BY foo.date DESC
    LIMIT 10
)
ORDER BY ABS(TIMESTAMPDIFF(SECOND, `foo`.`date`, NOW()))
LIMIT 10;        -- yes, repeated

Given `INDEX(is_relevant, date), that query will not read more than 20 rows to get the "closest" 10.

You say that the real query is much more complex. Well, it still might "start" with finding the nearest 10, then JOINing to the other tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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