List:General Discussion« Previous MessageNext Message »
From:Brandon Phelps Date:August 30 2011 4:45pm
Subject:Query Optimization
View as plain text  
Hello,

I have the following query I'd like to optimize a bit:

SELECT
	sc.open_dt,
	sc.close_dt,
	sc.protocol,
	INET_NTOA( sc.src_address ) AS src_address,
	sc.src_port,
	INET_NTOA( sc.dst_address ) AS dst_address,
	sc.dst_port,
	sc.sent,
	sc.rcvd,
	spm.desc AS src_port_desc,
	dpm.desc AS dst_port_desc
FROM firewall_connections AS sc
	LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
	LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE
	(open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30 00:00:00')
	AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '2011-08-30 12:36:53')
ORDER BY rcvd DESC
LIMIT 0 , 10

Currently I have an index on the rcvd column which seems to be working 
based on the output of EXPLAIN:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	sc 	index 	open_dt 	ndx_rcvd 	4 	NULL	10 	Using where
1 	SIMPLE 	spm 	eq_ref 	PRIMARY 	PRIMARY 	2 	syslog.sc.src_port 	1 	
1 	SIMPLE 	dpm 	eq_ref 	PRIMARY 	PRIMARY 	2 	syslog.sc.dst_port 	1 	

However the query is still fairly slow for some reason, any ideas how I 
could speed it up with additional indexes, etc?

The values I am using in the WHERE clause are variable and change each time.

The table has around 23 million records right now but is expected to 
continue to grow up to a potential 150 million.

Here is the table schema:
CREATE TABLE IF NOT EXISTS `firewall_connections` (
   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
   `open_dt` datetime NOT NULL,
   `close_dt` datetime NOT NULL,
   `protocol` smallint(6) NOT NULL,
   `src_address` int(10) unsigned NOT NULL,
   `src_port` smallint(5) unsigned NOT NULL,
   `dst_address` int(10) unsigned NOT NULL,
   `dst_port` smallint(5) unsigned NOT NULL,
   `sent` int(10) unsigned NOT NULL,
   `rcvd` int(10) unsigned NOT NULL,
   PRIMARY KEY (`id`),
   KEY `ndx_rcvd` (`rcvd`),
   KEY `ndx_sent` (`sent`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Thanks in advance!

-- 
Brandon
Thread
Query OptimizationBrandon Phelps30 Aug
  • Re: Query OptimizationMichael Dykman30 Aug
  • Re: Query OptimizationJochem van Dieten1 Sep
    • Re: Query OptimizationBrandon Phelps1 Sep
      • Re: Query OptimizationMySQL)1 Sep
        • Re: Query OptimizationBrandon Phelps1 Sep
          • Re: Query OptimizationBrandon Phelps8 Sep
            • Re: Query OptimizationAndrew Moore8 Sep
              • Re: Query OptimizationBrandon Phelps8 Sep
                • Re: Query OptimizationAndrew Moore8 Sep
                  • Re: Query OptimizationBrandon Phelps8 Sep
                    • Re: Query OptimizationDerek Downey8 Sep
                      • Re: Query OptimizationBrandon Phelps8 Sep
                        • Re: Query OptimizationMihail Manolov8 Sep
                          • Re: Query OptimizationBrandon Phelps8 Sep
                            • Re: Query OptimizationMihail Manolov8 Sep
                              • Re: Query OptimizationBrandon Phelps8 Sep
                • Re: Query OptimizationMihail Manolov8 Sep
                  • Re: Query OptimizationBrandon Phelps8 Sep
                    • Re: Query OptimizationAndrew Moore8 Sep
                      • Re: Query OptimizationBrandon Phelps8 Sep