***BOGO*** Re: Slow Query

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

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 <anandkl@xxxxxxxxx> 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 <darryl.steyn@xxxxxxxxx> 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 <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
>>>>
>>>
>>>
>>>
>>
>>
>