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