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,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
