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

Reply via email to