Also since mysql give date in Y-M-D you might want to remove the
"from_unixtime" and "date_format" formating functions
On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote:
>
> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
> 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, [EMAIL PROTECTED]
> > Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
> > Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
> > Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
> >
> >
>
