MySQL join with multiple ranges in 'WHERE' clause

I am trying to use snort's schema and extract information relevant to my program. I use the following query to extract alerts that have happened over a specified period of time for a given set of IPs.The query is something like

select event.sid,event.cid,event.signature,event.timestamp,iphdr.ip_proto,iphdr.ip_src,iphdr.ip_dst from 
event join iphdr 
where event.sid=iphdr.sid and event.cid=iphdr.cid and iphdr.ip_dst >= inet_aton('x.x.x.1') and iphdr.ip_dst <= inet_aton('x.x.x.255') 
or iphdr.ip_dst >= inet_aton('x.x.y.1') and iphdr.ip_dst <= inet_aton('x.x.y.255') 
and event.timestamp > "2011-05-06 00:00:00" order by timestamp 

Where I need to get the alerts that occur after specified time (06-May-2011) for two sets of IPs (x.x.x.0/24 and x.x.y.0/24)

Execution of the query for ONLY ONE set of IPs (say 192.168.1.0/24) (i.e. between 192.168.1.1 and 192.168.1.255) is fast and easy

The problem happens if there are multiple ranges in WHERE clause. E.g. IPs between 192.168.1.1 and 192.168.1.255 OR between 10.21.18.1 and 10.21.18.255. As given in query above

In such a case, the execution of the mysql query takes long time to execute (I ran it for 30 minutes and then had to kill MySQL daemon)

I am sure it is the OR part that is causing the problem but don't know how to开发者_运维问答 rectify it.

Both the event and iphdr tables have approx. 150,000 rows in all

Since in my program the user may specify multiple subnets for which he wants to see the alerts, I need the query to run seamlessly irrespective of the number of ranges in WHERE clause.

---

In SQL, AND has higher precedence that OR.

Your filter is in fact this:

(
event.sid=iphdr.sid and event.cid=iphdr.cid and iphdr.ip_dst >= inet_aton('x.x.x.1') and iphdr.ip_dst <= inet_aton('x.x.x.255')
)
or
(
iphdr.ip_dst >= inet_aton('x.x.y.1') and iphdr.ip_dst <= inet_aton('x.x.y.255') and event.timestamp > "2011-05-06 00:00:00"
)

The second conditions breaks correlation between iphdr and event which results in an cartesian join of their subsets.

Put the OR clause into brackets:

event.sid=iphdr.sid
AND event.cid=iphdr.cid
AND
(
iphdr.ip_dst BETWEEN inet_aton('x.x.x.1') AND inet_aton('x.x.x.255')
OR
iphdr.ip_dst BETWEEN inet_aton('x.x.y.1') AND inet_aton('x.x.y.255')
)
AND event.timestamp > "2011-05-06 00:00:00"

You may also want to create these indexes:

iphdr (ip_dst)
event (timestamp)

---

Your OR in the WHERE ruins your join. Try to get the join out of the where or at least use brackets around the OR, e.g.:

select event.sid,event.cid,event.signature,event.timestamp,
iphdr.ip_proto,iphdr.ip_src,iphdr.ip_dst 
from event join iphdr on (event.sid=iphdr.sid and event.cid=iphdr.cid)
where iphdr.ip_dst >= inet_aton('x.x.x.1') 
and (
iphdr.ip_dst <= inet_aton('x.x.x.255') 
or iphdr.ip_dst >= inet_aton('x.x.y.1') 
)
and iphdr.ip_dst <= inet_aton('x.x.y.255') 
and event.timestamp > "2011-05-06 00:00:00" 
order by timestamp