Can this be done with a single query?

--001485e989045d8356049268d0b2
Content-Type: text/plain; charset=ISO-8859-1

I have 2 tables: events and mappings.

what I want to do is something like:

SELECT DISTINCT(src_ip) FROM event WHERE timestamp BETWEEN '2010-10-11
00:00:00' AND '2010-10-12 00:00:00' 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.%.%';

but, within that somewhere also check to see if src_ip exists in mappings.
If it does, do not return it.

Is this possible?

Thanks.
--
Paul Halliday
Ideation | Individualization | Learner | Achiever | Analytical
http://www.pintumbler.org

--001485e989045d8356049268d0b2--

Re: Can this be done with a single query?

Paul,

I think you could accomplish this by adding a subquery to your where clause, like:

AND NOT EXISTS (SELECT * FROM mappings m where m.src_ip=src_ip)


Hope that helps.


Nathan


On Tue, Oct 12, 2010 at 03:19:36AM -0700, Paul Halliday wrote:
> I have 2 tables: events and mappings.
>
> what I want to do is something like:
>
> SELECT DISTINCT(src_ip) FROM event WHERE timestamp BETWEEN '2010-10-11
> 00:00:00' AND '2010-10-12 00:00:00' 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.%.%';
>
> but, within that somewhere also check to see if src_ip exists in mappings.
> If it does, do not return it.
>
> Is this possible?
>
> Thanks.
> --
> Paul Halliday
> Ideation | Individualization | Learner | Achiever | Analytical
> http://www.pintumbler.org

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Can this be done with a single query?

--001636498fd9fc7c4904926c164b
Content-Type: text/plain; charset=ISO-8859-1

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.

-JW


On Tue, Oct 12, 2010 at 5:19 AM, Paul Halliday wrote:

> I have 2 tables: events and mappings.
>
> what I want to do is something like:
>
> SELECT DISTINCT(src_ip) FROM event WHERE timestamp BETWEEN '2010-10-11
> 00:00:00' AND '2010-10-12 00:00:00' 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.%.%';
>
> but, within that somewhere also check to see if src_ip exists in mappings.
> If it does, do not return it.
>
> Is this possible?
>
> Thanks.
> --
> Paul Halliday
> Ideation | Individualization | Learner | Achiever | Analytical
> http://www.pintumbler.org
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001636498fd9fc7c4904926c164b--

Re: Can this be done with a single query?

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

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

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@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
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=gcdmg-mysql-2@m.gmane.org

RE: Can this be done with a single query?

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@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.halliday@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
> 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=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org