Re: Query Optimization

[Brandon Phelps]

Thanks Singer,  this took my query down to 0.0007, perfect!  I wasn't 
aware a single index of multiple columns would work when one of the 
columns was in the WHERE clause and the other in the ORDER BY clause. 
Learn something new every day I guess!


On 08/10/2011 02:03 PM, Singer X.J. Wang wrote:
> Try a index on (dst_port,close_dt)
>
> On Wed, Aug 10, 2011 at 14:01, Brandon Phelps <bphelps@stripped
> <mailto:bphelps@stripped>> 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
>
>     Thanks in advance,
>
>     --
>     Brandon
>
>     --
>     MySQL General Mailing List
>     For list archives: http://lists.mysql.com/mysql
>     To unsubscribe:
>     http://lists.mysql.com/mysql?__unsub=wang@ style="color:#666">stripped
>     <http://lists.mysql.com/mysql?unsub=1
>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>