Re: Query Optimization

[Jochem van Dieten]

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