There are a few things gonig on, but mainly it is the ORs that are killing
you.
As your require OR to examine two distinct columns, both of equal relevance
to the query, MySQL: is left with no choice but to do a full table scan on
what might be (at a guess) a very larger table. No amount of indexing will
fix this for the query presented.
You would be better off writing it as two distinct queires, each concerned
with conditions on a single column (open_dt and close_dt) and then UNIONing
the results. In this form, the indexes have a chance of being engaged.
Once the indexes are engaged, you probably want to your DATE/DATETIME
strings into actual DATEs or DATATIMEs, thus:
...
(open_dt >= DATE('2011-08-30 00:00:00'))
...
In it's current state, the DATE fields are being converted to strings
implicitly for every row tested which further frustrates index usage as the
index is against the quasi-numeric DATE, not the string representation which
your current implementation appears to expect. This query would also
suddenly begin to fail entirely if the DEFAULT_DATE_FORMAT gets modified by
an admin or a future release of MySQL. The explicit casting I have
suggested will protect you against that,
- michael dykman`
On Tue, Aug 30, 2011 at 12:45 PM, Brandon Phelps <bphe...@gls.com> wrote:
> 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=mdyk...@gmail.com<http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com>
>
>
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
