List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:August 30 2011 5:23pm
Subject:Re: Query Optimization
View as plain text  
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 <bphelps@stripped> 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=mdykman@ style="color:#666">stripped<http://lists.mysql.com/mysql?unsub=1
>
>


-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.

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