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 should look at the indices on the tables
involved. Try doing "explain" on the query:
EXPLAIN SELECT COUNT(event.src_ip) AS count,
INET_NTOA(event.src_ip),
mappings.cc
FROM event join mappings ON event.src_ip between mappings.start_ip and
mappings.end_ip
WHERE event.timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07
20:00:00'
GROUP BY event.src_ip
ORDER BY count DESC
LIMIT 20;
That'll give you some more information on what mysql is doing with
your data.
andy
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 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 20;
I am surprised by the quotes you have around the "start_ip" and "end_ip"
columns; to me, this makes that look like strings.
That because I don't know what I am doing :). No quotes on integers; got it!
From your posting, I see the result you hope to get but not the one you
actually get. IMO, just dropping the single quotes around the two column
names should produce the data you want to get.
Or what is the result you receive?
Removing the quotes does work. The query however took 1h15m to complete. Yuck.
I am guessing this is because even though there is a limit, it is
still doing the lookup on everything past 20. Also, if the first
address has a count of say 2000, it would be doing the lookup 2000
times for a single address.
Is that right?
If it is I guess I will have to post process the results. Which is
fine, I just like to keep as much in the queries as I can.
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
