Speed up query with REPEAT?

japhy1 · #1 (**permalink**) 11-24-04, 09:25

Hi,

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?

thanks in advance.

guelphdad · #2 (**permalink**) 11-24-04, 12:33

Is your table indexed? 100,000 rows is not all that large compared to what mysql can handle.

r937 · #3 (**permalink**) 11-24-04, 13:20

applying a function to a column value usually means that the index for that column will be ignored

yes, you should have an index

but you should also re-write your query like this --

Code:

 where timestampcolumn
       between 'low input value'
           and 'high input value'

in other words, use whatever application scripting language you're using to "pre-calculate" the bounds that you want, and then the index can be used

you should see a marked improvement in query speed

japhy1 · #4 (**permalink**) 11-24-04, 16:27

Thank you, this was helpful...

Internet Services

Web Development

Digital Marketing

Consumer Tech