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,
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
>> for the user to report on. That part of the query has to be there for it
>> work nicely.
>> 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
>>> "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.
> 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