From: Ananda Kumar (anandklgmail.com)
Date: Fri Jun 27 2008 - 09:25:23 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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 <darryl.steyngmail.com> 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 <anandklgmail.com> wrote:
>
>> what are the columns in namekey index
>>
>>
>> On 6/27/08, Darryl Steyn <darryl.steyngmail.com> 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
>>>
>>
>>
>>
>
>

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]