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<[email protected]> 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<[email protected]> 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?**>
>>>>>>> [email protected]<http**://lists.mysql.com/mysql?**
>>>>>>> [email protected]<http://lists.mysql.com/[email protected]>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>> --
>>>>> MySQL General Mailing List
>>>>> For list archives: http://lists.mysql.com/mysql
>>>>> To unsubscribe: http://lists.mysql.com/mysql?**
>>>>> [email protected]<http://lists.mysql.com/[email protected]>
>>>>>
>>>>>
>>>>
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/[email protected]
>>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[email protected]
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
