subject:"RE\\\\\\\: Can this be done with a single query\\\\\\\?"

Re: Can this be done with a single query?

2010-10-12 Thread Nathan Sullivan

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=arch...@jab.org

Re: Can this be done with a single query?

2010-10-12 Thread Johnny Withers

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 paul.halli...@gmail.comwrote:

 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
joh...@pixelated.net

Re: Can this be done with a single query?

2010-10-12 Thread Paul Halliday

On Tue, Oct 12, 2010 at 11:14 AM, Johnny Withers joh...@pixelated.netwrote:

 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!

RE: Can this be done with a single query?

2010-10-12 Thread Travis Ard

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.netwrote:

 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

RE: Can this be done with a single query?

2010-10-12 Thread Daevid Vincent

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.netwrote:
 
  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

Re: Can this be done with a single query?

Re: Can this be done with a single query?

Re: Can this be done with a single query?

RE: Can this be done with a single query?

RE: Can this be done with a single query?

5 matches

Site Navigation

Mail list logo

Footer information