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.
> 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.
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