Hi Paul, all!
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!
Like Andy replied: No quotes around column names (or expressions) you
want evaluated as variables, these quotes mark (constant) strings.
(There is another kind of quotes which serves to solve problems around
using large and small letters in names, I'll ignore that here.)
>
>> 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.
In general, Andy is right recommending "explain".
This case, however, may be a bit different:
>
> 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?
Yes, that may be.
First, don't forget that the query has to evaluate all events (within
your date/time limits) to find which IP addresses are the 20 with the
highest count.
Second, I fear the server may first perform the join on all your data
and only then do the WHERE, the GROUP BY, and the LIMIT. Which means it
would handle much more data than needed.
I guess you might see an enormous boost if you split up your statement
into two parts (syntax not checked):
INSERT INTO events_to_locate
SELECT COUNT(src_ip) AS count, src_ip FROM event
WHERE timestamp BETWEEN '2009-12-06 20:00' and '2009-12-07 20:00:00'
GROUP BY src_ip ORDER BY count DESC LIMIT 20;
SELECT count, INET_NTOA(events_to_locate.src_ip), mappings.cc
FROM events_to_locate, mappings
WHERE events_to_locate.src_ip BETWEEN mappings.start_ip AND
mappings.end_ip;
If your "mappings" table does not have a "src_ip" column (your column
names are unique in the tables listed in the FROM clause), you need not
give the table names in that second statement:
SELECT count, INET_NTOA(src_ip), cc
FROM events_to_locate, mappings
WHERE src_ip BETWEEN start_ip AND end_ip;
"Division of complexity":
Maybe the optimizer is not clever enough to apply this optimization,
then using a temporary table which holds just the 20 IP addresses you
are interested in should significantly reduce the amount of work.
>
> 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.
In general, doing work in the server is better, because it means you
need to transfer less data between server and client.
As usual, there are exceptions.
HTH,
Jörg
--
Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com
Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
