***BOGO*** Re: Slow Query

["Darryl Steyn" <darryl.steyn@xxxxxxxxx>]

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 <anandkl@xxxxxxxxx> wrote:

> what are the columns in namekey index
>
>
> On 6/27/08, Darryl Steyn <darryl.steyn@xxxxxxxxx> 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
>>
>
>