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,
>
