Hi Ananda,
At the moment the explain for the entire month will look the same as the one
I first gave as the database currently only has test data for the start of
the month.
I will populate the database with more info on Monday, but I have managed to
get it to return only the rows where the minute is 00 or 30 (enough for
reporting) which does seem to be helping (94rows, 40.27seconds).
Thanks,
Darryl
On Sat, Jun 28, 2008 at 8:39 AM, Ananda Kumar <[EMAIL PROTECTED]> wrote:
> The more data u fetch the more time the query takes,.
> Can u please check the explain plan for the entire month and lets us know.
>
> If the number of rows processed by db is more than 10% of the total rows,
> than optimizer considers doing a FULL TABLE scan better than a index scan
>
>
> On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote:
>
>> 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 withouting?
>>
>>
>> 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
>>>> >
>>>> >
>>>>
>>>
>>>
>>
>
