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