List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:September 1 2011 8:59am
Subject:Re: Query Optimization
View as plain text  
On Aug 30, 2011 6:46 PM, "Brandon Phelps" wrote:
> 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

If this is a firewall connection log I presume open_dt is the time a
connection was opened and is always going to be less than close_dt. Right?

> 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')

In that case your logic here simplifies to:
WHERE
  open_dt >= '2011-08-30 00:00:00'
  AND
  close_dt <= '2011-08-30 12:36:53'

Now add an index over open_dt and close_dt and see what happens.

Jochem

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