List:General Discussion« Previous MessageNext Message »
From:Darryl Steyn Date:June 27 2008 4:02pm
Subject:Re: Slow Query
View as plain text  
Hi Jörg,

I have applied the changes you have suggested and still no joy :(

I will carry on playing around with it to see if there is no way else for me
to simplify it (maybe selecting only every 30mins or so).

Thanks for the help,
Darryl

On Fri, Jun 27, 2008 at 5:28 PM, Joerg Bruehe <joerg@stripped> wrote:

> 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