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.
>
