On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe <Joerg.Bruehe@stripped> wrote:
> Hi everybody!
> Neil Aggarwal wrote:
>>> 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.