I'm using MySQL 4.1 to query a large table (>100,000 records). The table is ordered (asc) by timestamp...My query is a time comparison, basically i am looking for all records that possess timestamps closest to (within 5 seconds of) a given time that i input.
so, say i input a time like '2003-01-01 00:00:10'. I'm looking for an output like
-> '2003-01-01 00:00:08', '2003-01-01 00:00:14',etc. (along with the accompanying data from those records)
i am currently using the TIMEDIFF function to evaluate the time difference between my inputted value and every record, i.e.
ABS(TIME_TO_SEC(TIMEDIFF('my_input','tbl_value'))) < 5.
It works but is slow...can anyone help with a faster method? maybe using ORDER BY, and REPEAT somehow to not have to evaluate every record but stop the query when it passes the time window?