***BOGO*** Re: Slow Query

["Ananda Kumar" <anandkl@xxxxxxxxx>]

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
>