Hi Ananda,
The problem is the one date is stored as a unix time stamp, and the other as
a XXXX-XX-XX XX:XX:XX format :/. Can MySQL just match it without converting?
I have dropped the 2nd conversions on the WHERE part of the query as I can
format that before executing to look correct.
It's taking 4.5 seconds to execute when limiting to 10, the logs are
inserted every 5 mins and when you select an entire month you can imagine
how long it would take :/
Thanks for the help so far,
Darryl
On Fri, Jun 27, 2008 at 6:13 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote:
> 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
>> >
>> >
>>
>
>
