List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:October 12 2010 10:42pm
Subject:RE: Can this be done with a single query?
View as plain text  
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_ard@stripped] 
> Sent: Tuesday, October 12, 2010 9:51 AM
> To: 'Paul Halliday'
> Cc: mysql@stripped
> 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.halliday@stripped] 
> Sent: Tuesday, October 12, 2010 10:08 AM
> To: Johnny Withers
> Cc: mysql@stripped
> Subject: Re: Can this be done with a single query?
> 
> 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!
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 

Thread
Can this be done with a single query?Paul Halliday12 Oct
  • Re: Can this be done with a single query?Nathan Sullivan12 Oct
  • Re: Can this be done with a single query?Johnny Withers12 Oct
    • Re: Can this be done with a single query?Paul Halliday12 Oct
      • RE: Can this be done with a single query?Travis Ard12 Oct
        • RE: Can this be done with a single query?Daevid Vincent13 Oct