From: Peter Brawley
Date: August 10 2011 6:18pm
Subject: Re: Query Optimization
List-Archive: http://lists.mysql.com/mysql/225514
Message-Id: <4E42CB70.9020208@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 8/10/2011 1:01 PM, Brandon Phelps wrote:
> Hello all,
>
> I am using the query below and variations of it to query a database 
> with a TON of records.  Currently the database has around 11 million 
> records but it grows every day and should cap out at around 150 million.
>
> I am curious if there is any way I can better optimize the below 
> query, as currently it takes this query around 10 seconds to run but I 
> am sure this will get slower and slower as the database grows.
>
> SELECT
>     open_dt,
>     close_dt,
>     protocol,
>     INET_NTOA(src_address) AS src_address,
>     src_port,
>     INET_NTOA(dst_address) AS dst_address,
>     dst_port,
>     sent,
>     rcvd
> FROM connections
> WHERE
>     dst_port = 80
> ORDER BY close_dt  DESC
> LIMIT 0, 30
>
> I do have an index on the dst_port column, as you can see by the 
> output of EXPLAIN:
>
> id               1
> select_type      SIMPLE
> table            connections
> type             ref
> possible_keys    dst_port
> key              dst_port
> key_len          2
> ref              const
> rows             1109273
> Extra            Using where; Using filesort
Did you try adding your ORDER BY argument close_dt to the index?

PB

-----
>
> Thanks in advance,
>