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