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