From: Paul Halliday Date: December 10 2009 6:11pm Subject: Re: Join on a where clause. List-Archive: http://lists.mysql.com/mysql/219631 Message-Id: <2dab70a30912101011k3b6792ebi34a5bfe724165828@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Dec 9, 2009 at 2:24 PM, Joerg Bruehe 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.