List:General Discussion« Previous MessageNext Message »
From:Brandon Phelps Date:September 1 2011 1:42pm
Subject:Re: Query Optimization
View as plain text  
On 09/01/2011 04:59 AM, Jochem van Dieten 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

Jochem,

I can't really use your WHERE logic because I also need to retrieve 
results where the open_dt time is out of the range specified.  For 
example, a very large file download might span multiple days so given 
your logic if the connection was started 2 days ago and I want to pull 1 
days worth of connections, I would miss that entry.  Basically I want to 
SELECT all of the records that were opened OR closed during the 
specified time period, ie. if any activity happened between my start and 
end dates, I need to see that record.

Any other ideas?

Thanks again,
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