Most efficient way to find the nearest integer in MySQL?

Question

I have a table in a MySQL database from which I want to select a row with the closest timestamp to another given timestamp.

time is a timestamp column (integer UNIX timestamp). I chose 1250710000 arbitrarily.

This is the query I came up with, and I wonder if there is a more efficient way to do this:

 SELECT *, ABS(time - 1250710000) AS time_dist FROM table ORDER BY time_dist ASC LIMIT 1 

Is this the best way to do this?

Answer 1

Assuming time indexed, you can get the following entry almost for free:

 SELECT * FROM table WHERE time > 1250710000 ORDER BY time LIMIT 1 

And if I'm not mistaken, then the same should apply to the previous record, MySQL will just read the index in the reverse order. Use the UNION of the two, order them by diff and voila! The result will look like this:

 SELECT * FROM ( (SELECT *, ABS(time - 1250710000) AS time_diff FROM table WHERE time > 1250710000 ORDER BY time ASC LIMIT 1) UNION ALL (SELECT *, ABS(time - 1250710000) AS time_diff FROM table WHERE time < 1250710000 ORDER BY time DESC LIMIT 1) ) AS tmp ORDER BY time_diff LIMIT 1 

Ideally, instead of > and < you should use >= and <= and exclude the reference record using your primary identifier to account for records sharing the same timestamp.

Answer 2

As Evan said, the way it is. I would recommend the index in the timestamp field so that MySQL can crawl the smaller index rather than the entire table. In addition, I would try boxing to see if the index can speed things up:

 SELECT *, ABS(time - 1250710000) AS time_dist FROM table WHERE time between(1250610000,1250810000) ORDER BY time_dist ASC LIMIT 1 

The above limits allow you to request approximately +/- 1 day. You will need to do some tests to find out if an additional index scan (where clause) is faster than calculating ABS () for all the records in the table.

Answer 3

It would be more efficient to choose a minimum time that is longer and a maximum time less than just abs. This should avoid using the whole table.

SELECT MAX (time) AS prev WHERE time <1250710000;

SELECT MIN (time) AS next WHERE time> 1250710000;

CHOICE OF MIN (ABS (previous), ABS (further));

My SQL is not strong enough to combine them into one, and the overhead of three queries can kill any savings, but it is possible.