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:
SELECT DISTINCT(e.src_ip) 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; -Travis -----Original Message----- From: Paul Halliday [mailto:paul.halli...@gmail.com] Sent: Tuesday, October 12, 2010 10:08 AM To: Johnny Withers Cc: mysql@lists.mysql.com Subject: Re: Can this be done with a single query? On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers <joh...@pixelated.net>wrote: > 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 > INET_ATON(172.16.255.255) > AND src_ip NOT BETWEEN INET_ATON('192.168.0.0') AND > INET_ATON(192.168.255.255) > > 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 > tables. > > 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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
