List:General Discussion« Previous MessageNext Message »
From:Paul Halliday Date:December 10 2009 6:11pm
Subject:Re: Join on a where clause.
View as plain text  
On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe <Joerg.Bruehe@stripped> 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.
Thread
Join on a where clause.Paul Halliday9 Dec
  • RE: Join on a where clause.Neil Aggarwal9 Dec
    • Re: Join on a where clause.Joerg Bruehe9 Dec
      • RE: Join on a where clause.Neil Aggarwal9 Dec
      • Re: Join on a where clause.Paul Halliday10 Dec
        • Re: Join on a where clause.Andy Wallace10 Dec
        • Re: Join on a where clause.Joerg Bruehe12 Dec
  • Re: Join on a where clause.Shawn Green12 Dec