Query Optimization

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=gcdmg-mysql-2@m.gmane.org

Re: Query Optimization

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 > > 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@singerwang.com
>
>
>
> --
> The best compliment you could give Pythian for our service is a referral.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query Optimization

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=gcdmg-mysql-2@m.gmane.org