You may get better performance from your query, and be able to make better
use of indexes if you use integer comparisons for your IP address
expressions instead of converting to strings with pattern matching. You
might consider something like the following:
FROM event e
left outer join mappings m on mappings.src_ip = e.src_ip
WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
AND e.src_ip NOT BETWEEN 167772160 AND 184549375
AND e.src_ip NOT BETWEEN 2886729728 AND 2886795263
AND e.src_ip NOT BETWEEN 3232235520 AND 3232301055
AND m.src_ip IS NULL;
From: Paul Halliday [mailto:paul.halliday@stripped]
Sent: Tuesday, October 12, 2010 10:08 AM
To: Johnny Withers
Subject: Re: Can this be done with a single query?
On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers
> I would try:
> SELECT DISTINCT(e.src_ip)
> FROM event AS e
> LEFT JOIN mappings AS m ON e.src_ip=m.src_ip
> WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00'
> AND INET_NTOA(e.src_ip) NOT LIKE '10.%.%.%'
> AND INET_NTOA(e.src_ip) NOT LIKE '172.16.%.%'
> AND INET_NTOA(e.src_ip) NOT LIKE '192.168.%.%'
> AND m.src_ip IS NULL
> I would also modify the where clause to use:
> AND src_ip NOT BETWEEN INET_ATON('10.0.0.0') AND INET_ATON(10.255.255.255)
> AND src_ip NOT BETWEEN INET_ATON('172.16.0.0') AND
> AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND
> instead of
> AND INET_NTOA(src_ip) NOT LIKE '10.%.%.%'
> AND INET_NTOA(src_ip) NOT LIKE '172.16.%.%'
> AND INET_NTOA(src_ip) NOT LIKE '192.168.%.%
> You should also ensure there is an index on src_ip in events and mappings
> Using the INET_NTOA() function on the src_ip column will prevent index
> usage during the query.
This and the suggestion by Nathan both work.
Thanks for the help!