Need some help with select datetime range performance

  • Filter
  • Time
  • Show
Clear All
new posts

  • Need some help with select datetime range performance

    I have a db at work a colleague created. I don't have the ability to change the schema, but I could make a case for it if there is an opportunity for some speed improvements.

    The app I have has some results in it that have dates. So when I get the results I do a select that looks something like this:

    select time, text from results
    results.error_level_id = 100 and
    results.log_source_id = 200 and
    time >= '2008-08-13 15:00' and
    time <= '2008-08-14 15:00'

    I wouldn't think that it would but this query takes about 136s according to the MySQL query browser. Even when the result is an empty set.

    So I have a few questions:

    Why does it take so long?
    How can I make it faster?

    Thanks in advance!

  • #2
    I recommend gathering the following information:

    1) The EXPLAIN plan for your query
    2) SHOW TABLE STATUS LIKE 'results'
    3) SHOW INDEXES FROM results

    After that, someone here might have a suggestion.