Hello Paul,
Paul Halliday wrote:
I have 2 tables:
1) Event Data
2) Mappings
The query should return something like this:
Hits IP Country Code
20213.136.52.29 SE
I am trying this:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc
Hi Paul, all!
Paul Halliday wrote:
On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote:
Hi everybody!
Neil Aggarwal wrote:
Paul:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06
On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe joerg.bru...@sun.com wrote:
Hi everybody!
Neil Aggarwal wrote:
Paul:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06 20:00' and '2009-12-07 20:00:00' AND
A couple of thoughts - it's not no quotes on integers, but
no quotes around column references. When you use 'mappings.end_ip',
you are saying the string mappings.end_ip, and not referring to
a column in the mappings table. It just becomes a constant at that
point.
As for the performance, you
Paul:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN
'mappings.start_ip' AND 'mappings.end_ip' GROUP BY event.src_ip ORDER
BY count DESC LIMIT
Hi everybody!
Neil Aggarwal wrote:
Paul:
SELECT COUNT(event.src_ip) AS count, INET_NTOA(event.src_ip),
mappings.cc FROM event, mappings WHERE event.timestamp BETWEEN
'2009-12-06 20:00' and '2009-12-07 20:00:00' AND event.src_ip BETWEEN
'mappings.start_ip' AND 'mappings.end_ip' GROUP BY
Joerg:
A matching column is called an equijoin
However, that is not mandatory / the only form.
As long as the problem can be solved using ranges (or multiple ranges)
which do not overlap, the join should solve it.
I just learned something. Thanks for the info!
Neil
--
Neil
on some massive tables
and would like to optimise the join if possible
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of
Harald Fuchs
Sent: Wednesday, February 26, 2003 11:26 PM
To: [EMAIL PROTECTED]
Subject: Re: JOIN compared to WHERE clause
In article [EMAIL
hi i was wondering which statement is quicker getting results when joining
tables ? i presume something like FROM foo f LEFT JOIN bar b ON f.id=b.id is
quiker than WHERE f.id=b.id ?
SQL !! (ignore this)
-
Before posting,