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