Absolutely was just going to suggest this Travis. Another option and this is untested, but is to use HAVING and an alias. Something to this effect...
SELECT INET_NTOA(e.src_ip) AS source_ip WHERE e.timestamp BETWEEN '2010-10-11 00:00:00' AND '2010-10-12 00:00:00' HAVING source_ip BETWEEN '10.0.0.0' and '10.255.255.255' But yeah, Travis suggestion is cleaner and more efficient. > -----Original Message----- > From: Travis Ard [mailto:travis_...@hotmail.com] > Sent: Tuesday, October 12, 2010 9:51 AM > To: 'Paul Halliday' > Cc: mysql@lists.mysql.com > Subject: RE: Can this be done with a single query? > > 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=dae...@daevid.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
