From: Paul Halliday
Date: October 12 2010 4:08pm
Subject: Re: Can this be done with a single query?
List-Archive: http://lists.mysql.com/mysql/223289
Message-Id: <AANLkTin7DqnB0+GbVVDh+bD3UhmoT34HsoXykTHRsLqx@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary=000e0cd341c27f889c04926daef0

--000e0cd341c27f889c04926daef0
Content-Type: text/plain; charset=ISO-8859-1

On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers <johnny@stripped>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!

--000e0cd341c27f889c04926daef0--