Hi Ananda, The query is for reporting purposes and I would like to include a date range for the user to report on. That part of the query has to be there for it to work nicely.
Regards, Darryl On Fri, Jun 27, 2008 at 4:25 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: > 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 >>>> >>> >>> >>> >> >> >
