List:General Discussion« Previous MessageNext Message »
From:Joerg Bruehe Date:June 27 2008 3:28pm
Subject:Re: Slow Query
View as plain text  
Hi Darryl, all,


Darryl Steyn wrote:
> Hi Ananda,
> 
> The query is for reporting purposes and I would like to include a date range
> for the user to report on. That part of the query has to be there for it to
> work nicely.
> 
> Regards,
> Darryl
> 
> On Fri, Jun 27, 2008 at 4:25 PM, Ananda Kumar <anandkl@stripped> wrote:
> 
>> Hi Darryl,
>> Indexing looks fine, but what are ur trying to achive using this conditions
>>
>> "cache.server.tstamp > 0) AND
>> ((date_format(cache.server.tstamp,'%Y-%m-%d') BETWEEN "2008-05-31" AND
>> "2008-06-10" ))"
>>

IMO, it might help if you could code your condition(s) such that the 
format conversion is applied only once (on the constant values),
and not on each row.

With your current query, each row's "tstamp" value must be converted to 
evaluate the condition.
If you would convert the values "2008-05-31" and "2008-06-10" to the 
format of your column, you would avoid that (and so reduce load):

    cache.server.tstamp > 0) AND
   (cache.server.tstamp BETWEEN  conversion ("2008-05-31 00:00:00") AND
                                 conversion ("2008-06-10 23:59:59") )

The correct "conversion" depends on your column's data type.

Also, your condition "cache.server.tstamp > 0" should not be necessary, 
as the BETWEEN will also ensure that.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg@stripped
Sun Microsystems GmbH,   Sonnenallee 1,   D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering     Muenchen: HRB161028

Thread
Slow QueryDarryl Steyn27 Jun
  • Re: Slow QueryAnanda Kumar27 Jun
    • Re: Slow QueryDarryl Steyn27 Jun
      • Re: Slow QueryAnanda Kumar27 Jun
        • Re: Slow QueryDarryl Steyn27 Jun
          • Re: Slow Querymos27 Jun
          • Re: Slow QueryJoerg Bruehe27 Jun
            • Re: Slow QueryDarryl Steyn27 Jun
              • Re: Slow QueryAnanda Kumar27 Jun
                • Re: Slow QueryDarryl Steyn27 Jun
                  • Re: Slow QueryAnanda Kumar28 Jun
                    • Re: Slow QueryDarryl Steyn28 Jun
                      • Re: Slow QueryMoon's Father30 Jun