List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 10 2011 6:18pm
Subject:Re: Query Optimization
View as plain text  
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,
>
Thread
Query OptimizationBrandon Phelps10 Aug
  • Re: Query OptimizationPeter Brawley10 Aug
Re: Query OptimizationBrandon Phelps10 Aug
Re: Query OptimizationJan Steinman10 Aug