Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004

    Unanswered: Speed up query with REPEAT?


    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.

  2. #2
    Join Date
    Mar 2004
    Is your table indexed? 100,000 rows is not all that large compared to what mysql can handle.

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    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 --
     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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Thank you, this was helpful...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts