List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:December 10 2009 6:48pm
Subject:Re: Join on a where clause.
View as plain text  
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.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