How about: 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 sonicwall_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 BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'
UNION 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 sonicwall_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 close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58' On Sep 8, 2011, at 3:25 PM, Brandon Phelps wrote: > Thanks for the idea Derek, however given the following query my EXPLAIN > output is identical: > > 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 sonicwall_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 ('2011-09-07 13:18:58' <= open_dt <= '2011-09-08 13:18:58') OR > ('2011-09-07 13:18:58' <= close_dt <= '2011-09-08 13:18:58'); > > > +----+-------------+-------+--------+---------------+---------+---------+--------------------+----------+-------------+ > | id | select_type | table | type | possible_keys | key | key_len | ref > | rows | Extra | > +----+-------------+-------+--------+---------------+---------+---------+--------------------+----------+-------------+ > | 1 | SIMPLE | sc | ALL | NULL | NULL | NULL | > NULL | 32393330 | 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 | | > +----+-------------+-------+--------+---------------+---------+---------+--------------------+----------+-------------+ > > I did create indexes on open_dt and close_dt (2 separate indexes). > > > > On 09/08/2011 02:55 PM, Derek Downey wrote: >> Correct me if I'm wrong. You're wanting to get all records that have an >> open_date or a close_date between two times. >> >> If that's correct, you might be able to get an index_merge by doing a query >> like: >> >> WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting >> time)<=close_dt<=(ending time)) >> >> and creating two indexes (one on 'open_dt' and the other on 'close_dt') >> >> http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html >> >> Regards, >> Derek >> >> On Sep 8, 2011, at 2:50 PM, Brandon Phelps wrote: >> >>> Andy, >>> >>> The queries take minutes to run. MySQL is 5.1.54 and it's running on >>> Ubuntu server 11.04. Unfortunately the machine only has 2GB of RAM but no >>> other major daemons are running on the machine. We are running RAID 1 >>> (mirroring) with 1TB drives. The tables in question here are all MyISAM. >>> When running with the LIMIT 10 my EXPLAIN is: >>> >>> +----+-------------+-------+--------+---------------------+---------------------+---------+--------------------+----------+-----------------------------+ >>> | id | select_type | table | type | possible_keys | key >>> | key_len | ref | rows | Extra >>> | >>> +----+-------------+-------+--------+---------------------+---------------------+---------+--------------------+----------+-----------------------------+ >>> | 1 | SIMPLE | sc | range | ndx_open_close_rcvd | >>> ndx_open_close_rcvd | 8 | NULL | 32393316 | Using >>> where; Using filesort | >>> | 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 | >>> | >>> +----+-------------+-------+--------+---------------------+---------------------+---------+--------------------+----------+-----------------------------+ >>> >>> When I remove the LIMIT 10 I get: >>> >>> ----+-------------+-------+--------+---------------------+---------+---------+--------------------+----------+-----------------------------+ >>> | id | select_type | table | type | possible_keys | key | >>> key_len | ref | rows | Extra | >>> +----+-------------+-------+--------+---------------------+---------+---------+--------------------+----------+-----------------------------+ >>> | 1 | SIMPLE | sc | ALL | ndx_open_close_rcvd | NULL | NULL >>> | NULL | 32393330 | Using where; Using filesort | >>> | 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 | | >>> +----+-------------+-------+--------+---------------------+---------+---------+--------------------+----------+-----------------------------+ >>> >>> Thanks for all your help thus far. >>> >>> On 09/08/2011 02:38 PM, Andrew Moore wrote: >>>> I don't think I saw any query timings in the emails (maybe I missed them). >>>> >>>> What version of MySQL are you currently using? >>>> What does the explain look like when your remove the limit 10? >>>> Is your server tuned for MyISAM or InnoDB? >>>> What kind of disk setup is in use? >>>> How much memory is in your machine? >>>> >>>> >>>> On Thu, Sep 8, 2011 at 7:27 PM, Brandon Phelps<bphe...@gls.com> wrote: >>>> >>>>> Thanks for the reply Andy. Unfortunately the users will be selecting >>>>> varying date ranges and new data is constantly coming in, so I am not sure >>>>> how I could archive/cache the necessary data that would be any more >>>>> efficient than simply using the database directly. >>>>> >>>>> >>>>> >>>>> On 09/08/2011 02:16 PM, Andrew Moore wrote: >>>>> >>>>>> Thinking outside the query, is there any archiving that could happen to >>>>>> make >>>>>> your large tables kinder in the range scan? >>>>>> >>>>>> Andy >>>>>> >>>>>> On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps<bphe...@gls.com> wrote: >>>>>> >>>>>> On 09/01/2011 01:32 PM, Brandon Phelps wrote: >>>>>>> >>>>>>> On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote: >>>>>>>> >>>>>>>> On 9/1/2011 09:42, Brandon Phelps wrote: >>>>>>>>> >>>>>>>>> On 09/01/2011 04:59 AM, Jochem van Dieten wrote: >>>>>>>>>> >>>>>>>>>>> ... >>>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>> 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? >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> I believe Jochem was on the right track but he got his dates >>>>>>>>> reversed. >>>>>>>>> >>>>>>>>> Let's try a little ASCII art to show the situation. I will setup a >>>>>>>>> query >>>>>>>>> window with two markers (s) and (e). Events will be marked by |----| >>>>>>>>> markers >>>>>>>>> showing their durations. >>>>>>>>> >>>>>>>>> a) (s) (e) >>>>>>>>> b) |---| >>>>>>>>> c) |---| >>>>>>>>> d) |---| >>>>>>>>> e) |--------------------| >>>>>>>>> f) |---| >>>>>>>>> g) |---| >>>>>>>>> >>>>>>>>> To describe these situations: >>>>>>>>> a) is the window for which you want to query (s) is the starting time >>>>>>>>> and >>>>>>>>> (e) is the ending time for the date range you are interested in. >>>>>>>>> b) the event starts and stops before your window exists. It won't be >>>>>>>>> part >>>>>>>>> of your results. >>>>>>>>> c) the event starts before the window but ends within the window - >>>>>>>>> include this >>>>>>>>> d) the event starts and ends within the window - include this >>>>>>>>> e) the event starts before the window and ends after the window - >>>>>>>>> include >>>>>>>>> this >>>>>>>>> f) the event starts inside the window but ends beyond the window - >>>>>>>>> include this. >>>>>>>>> g) the event starts and ends beyond the window - exclude this. >>>>>>>>> >>>>>>>>> In order to get every event in the range of c-f, here is what you need >>>>>>>>> for a WHERE clause >>>>>>>>> >>>>>>>>> WHERE start<= (ending time) and end>= (starting time) >>>>>>>>> >>>>>>>>> Try that and let us know the results. >>>>>>>>> >>>>>>>>> >>>>>>>> Thanks Jochem and Shawn, however the following two queries result in >>>>>>>> the >>>>>>>> exact same EXPLAIN output: (I hope the tables don't wrap too early for >>>>>>>> you) >>>>>>>> >>>>>>>> Old method: >>>>>>>> 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-31 09:53:31' OR close_dt>= '2011-08-31 09:53:31') >>>>>>>> AND (open_dt<= '2011-09-01 09:53:31' OR close_dt<= '2011-09-01 >>>>>>>> 09:53:31') >>>>>>>> ORDER BY rcvd DESC >>>>>>>> LIMIT 0, 10; >>>>>>>> >>>>>>>> New method with BTREE index on open_dt, close_dt (index name is >>>>>>>> ndx_open_close_dt): >>>>>>>> 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-09-01 09:53:31' AND close_dt>= '2011-08-31 09:53:31' >>>>>>>> ORDER BY rcvd DESC >>>>>>>> LIMIT 0, 10; >>>>>>>> >>>>>>>> EXPLAIN output for old method: >>>>>>>> +----+-------------+-------+--****------+---------------------**--** >>>>>>>> ----+----------+---------+----****----------------+------+----** >>>>>>>> --**-------+ >>>>>>>> | id | select_type | table | type | possible_keys | key | key_len | ref >>>>>>>> | >>>>>>>> rows | Extra | >>>>>>>> +----+-------------+-------+--****------+---------------------**--** >>>>>>>> ----+----------+---------+----****----------------+------+----** >>>>>>>> --**-------+ >>>>>>>> | 1 | SIMPLE | sc | index | open_dt,ndx_open_close_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 | | >>>>>>>> +----+-------------+-------+--****------+---------------------**--** >>>>>>>> ----+----------+---------+----****----------------+------+----** >>>>>>>> --**-------+ >>>>>>>> >>>>>>>> EXPLAIN output for new method with new index: >>>>>>>> +----+-------------+-------+--****------+---------------------**--** >>>>>>>> ----+----------+---------+----****----------------+------+----** >>>>>>>> --**-------+ >>>>>>>> | id | select_type | table | type | possible_keys | key | key_len | ref >>>>>>>> | >>>>>>>> rows | Extra | >>>>>>>> +----+-------------+-------+--****------+---------------------**--** >>>>>>>> ----+----------+---------+----****----------------+------+----** >>>>>>>> --**-------+ >>>>>>>> | 1 | SIMPLE | sc | index | open_dt,ndx_open_close_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 | | >>>>>>>> +----+-------------+-------+--****------+---------------------**--** >>>>>>>> ----+----------+---------+----****----------------+------+----** >>>>>>>> --**-------+ >>>>>>>> >>>>>>>> SHOW INDEX: >>>>>>>> +----------------------+------****------+-------------------+-**--** >>>>>>>> -----------+-------------+----****-------+-------------+------**--** >>>>>>>> --+--------+------+-----------****-+---------+ >>>>>>>> | Table | Non_unique | Key_name | Seq_in_index | Column_name | >>>>>>>> Collation >>>>>>>> | >>>>>>>> Cardinality | Sub_part | Packed | Null | Index_type | Comment | >>>>>>>> +----------------------+------****------+-------------------+-**--** >>>>>>>> -----------+-------------+----****-------+-------------+------**--** >>>>>>>> --+--------+------+-----------****-+---------+ >>>>>>>> | firewall_connections | 1 | ndx_open_close_dt | 1 | open_dt | A | >>>>>>>> 1342691 >>>>>>>> | NULL | NULL | | BTREE | | >>>>>>>> | firewall_connections | 1 | ndx_open_close_dt | 2 | close_dt | A | >>>>>>>> 6377783 | NULL | NULL | | BTREE | | >>>>>>>> +----------------------+------****------+-------------------+-**--** >>>>>>>> -----------+-------------+----****-------+-------------+------**--** >>>>>>>> --+--------+------+-----------****-+---------+ >>>>>>>> >>>>>>>> >>>>>>>> Although right now the queries do seem to be executing much faster, >>>>>>>> although I'm not quite sure why. And I'm not sure why the new >>>>>>>> ndx_open_close_dt isn't being used either. >>>>>>>> >>>>>>>> -Brandon >>>>>>>> >>>>>>>> >>>>>>>> I am still having a big issue with my query as seen above. The table >>>>>>> is up >>>>>>> to around 32 million records at the moment and either of the two SELECT >>>>>>> queries above take a very long time to run. Is there anything at all I >>>>>>> can >>>>>>> do to speed things up? It seems that changing the format of the WHERE >>>>>>> clause did not help at all, as the EXPLAIN output is exactly the same >>>>>>> for >>>>>>> both version. I also tried adding an index on (open_dt, close_dt, rcvd) >>>>>>> but >>>>>>> that index does not get used. >>>>>>> >>>>>>> Any other ideas? >>>>>>> >>>>>>> Thanks in advance, >>>>>>> >>>>>>> >>>>>>> Brandon >>>>>>> >>>>>>> -- >>>>>>> MySQL General Mailing List >>>>>>> For list archives: http://lists.mysql.com/mysql >>>>>>> To unsubscribe: >>>>>>> http://lists.mysql.com/mysql?****<http://lists.mysql.com/mysql?**> >>>>>>> unsub=eroomy...@gmail.com<http**://lists.mysql.com/mysql?** >>>>>>> unsub=eroomy...@gmail.com<http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com> >>>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> -- >>>>> MySQL General Mailing List >>>>> For list archives: http://lists.mysql.com/mysql >>>>> To unsubscribe: http://lists.mysql.com/mysql?** >>>>> unsub=eroomy...@gmail.com<http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com> >>>>> >>>>> >>>> >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: http://lists.mysql.com/mysql?unsub=de...@orange-pants.com >>> >> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mmano...@liquidation.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org