List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:December 27 2001 1:36am
Subject:RE: Select slow with ORDER BY ... DESC
View as plain text  
* Eldon Ziegler
>
> mysql is slow when finding the last value before a period of time (I'm
> plotting the temperature values during a period of time and want the last
> value before the period begins).
>
> The query is:
> select DateTime, Target, Value from log
>    where DateTime < "2001-12-21 00:00:00"
>       and Source = "StatIO#1"
>       and Target = "SN1"
>       and (Attribute = NULL or Attribute = "" or Attribute = "T")
>           order by DateTime desc limit 1;
>
> The table has two indexes,
> 1. DateTime only
> 2. dtSrcTarAttr on log (DateTime, Source, Target, Attribute)
>
> "Explain" shows the query using the DateTime index instead of the
> dtSrcTarAttr that contains all items in the WHERE clause.
>
> How can I speed up this query?

Change (DateTime, Source, Target, Attribute) to (Source, Target, DateTime,
Attribute)

--
Roger

Thread
Select slow with ORDER BY ... DESCEldon Ziegler24 Dec
  • RE: Select slow with ORDER BY ... DESCRoger Baklund27 Dec