Slow Query
Hi, I am running a query that I feel shouldn't be taking as long as it does to execute. The query is as follows; SELECT traffic.trafficin.bytes_in as bytes_in, round(cache.server.serverallkbytes_in) as serverallkbytes_in, cache.server.tstamp as tstamp FROM traffic.trafficin LEFT JOIN cache.server ON(from_unixtime(traffic.trafficin.time,"%Y %M %d %H %i")=date_format(cache.server.tstamp,"%Y %M %d %H %i")) WHERE ( traffic.trafficin.name="em0" AND cache.server.tstamp > 0) AND ((date_format(cache.server.tstamp,'%Y-%m-%d') BETWEEN "2008-05-31" AND "2008-06-10" )) Here's the output from the explain; ++-+---+---+---+-+-+---+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+-+-+---+---+-+ | 1 | SIMPLE | server | range | PRIMARY | PRIMARY | 4 | NULL | 3952 | Using where | | 1 | SIMPLE | trafficin | ref | namekey,nameindex | namekey | 30 | const | 25867 | Using where | ++-+---+---+---+-+-+---+---+-+ The strange thing is, when I remove the date range the query executes in an acceptable time. Can anyone maybe point me in the right direction? Thanks, Darryl
Re: Slow Query
Hi, >From the traffic.trafficin table; UNIQUE KEY `namekey` (`name`,`time`), KEY `nameindex` (`name`), KEY `dateindex` (`date`), KEY `timeindex` (`time`) >From the cache.server table; PRIMARY KEY (`tstamp`) Thanks, Darryl On Fri, Jun 27, 2008 at 2:31 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > what are the columns in namekey index > > > On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote: >> >> Hi, >> >> I am running a query that I feel shouldn't be taking as long as it does to >> execute. The query is as follows; >> >> SELECT traffic.trafficin.bytes_in as bytes_in, >> round(cache.server.serverallkbytes_in) as serverallkbytes_in, >> cache.server.tstamp as tstamp FROM traffic.trafficin LEFT JOIN >> cache.server >> ON(from_unixtime(traffic.trafficin.time,"%Y %M %d %H >> %i")=date_format(cache.server.tstamp,"%Y %M %d %H %i")) WHERE ( >> traffic.trafficin.name="em0" AND cache.server.tstamp > 0) AND >> ((date_format(cache.server.tstamp,'%Y-%m-%d') BETWEEN "2008-05-31" AND >> "2008-06-10" )) >> >> Here's the output from the explain; >> >> >> ++-+---+---+---+-+-+---+---+-+ >> | id | select_type | table | type | possible_keys | key | >> key_len | ref | rows | Extra | >> >> ++-+---+---+---+-+-+---+---+-+ >> | 1 | SIMPLE | server | range | PRIMARY | PRIMARY | 4 >> | NULL | 3952 | Using where | >> | 1 | SIMPLE | trafficin | ref | namekey,nameindex | namekey | >> 30 | const | 25867 | Using where | >> >> ++-+---+---+---+-+-+---+---+-+ >> >> The strange thing is, when I remove the date range the query executes in >> an >> acceptable time. Can anyone maybe point me in the right direction? >> >> Thanks, >> Darryl >> > >
Re: Slow Query
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" ))" > > > > > > On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote: >> >> Hi, >> >> From the traffic.trafficin table; >> UNIQUE KEY `namekey` (`name`,`time`), >> KEY `nameindex` (`name`), >> KEY `dateindex` (`date`), >> KEY `timeindex` (`time`) >> >> From the cache.server table; >> PRIMARY KEY (`tstamp`) >> >> Thanks, >> Darryl >> >> On Fri, Jun 27, 2008 at 2:31 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >>> what are the columns in namekey index >>> >>> >>> On 6/27/08, Darryl Steyn <[EMAIL PROTECTED]> wrote: >>>> >>>> Hi, >>>> >>>> I am running a query that I feel shouldn't be taking as long as it does >>>> to >>>> execute. The query is as follows; >>>> >>>> SELECT traffic.trafficin.bytes_in as bytes_in, >>>> round(cache.server.serverallkbytes_in) as serverallkbytes_in, >>>> cache.server.tstamp as tstamp FROM traffic.trafficin LEFT JOIN >>>> cache.server >>>> ON(from_unixtime(traffic.trafficin.time,"%Y %M %d %H >>>> %i")=date_format(cache.server.tstamp,"%Y %M %d %H %i")) WHERE ( >>>> traffic.trafficin.name="em0" AND cache.server.tstamp > 0) AND >>>> ((date_format(cache.server.tstamp,'%Y-%m-%d') BETWEEN "2008-05-31" AND >>>> "2008-06-10" )) >>>> >>>> Here's the output from the explain; >>>> >>>> >>>> ++-+---+---+---+-+-+---+---+-+ >>>> | id | select_type | table | type | possible_keys | key | >>>> key_len | ref | rows | Extra | >>>> >>>> ++-+---+---+---+-+-+---+---+-+ >>>> | 1 | SIMPLE | server | range | PRIMARY | PRIMARY | 4 >>>> | NULL | 3952 | Using where | >>>> | 1 | SIMPLE | trafficin | ref | namekey,nameindex | namekey | >>>> 30 | const | 25867 | Using where | >>>> >>>> ++-+---+---+---+-+-+---+---+-+ >>>> >>>> The strange thing is, when I remove the date range the query executes in >>>> an >>>> acceptable time. Can anyone maybe point me in the right direction? >>>> >>>> Thanks, >>>> Darryl >>>> >>> >>> >>> >> >> >
Re: Slow Query
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
>
>
Re: Slow Query
Hi Ananda,
The problem is the one date is stored as a unix time stamp, and the other as
a -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
>> >
>> >
>>
>
>
Re: Slow Query
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 -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
>>>> >
>>>> >
>>>>
>>>
>>>
>>
>
