MySQL Forums
Forum List  »  Optimizer & Parser

Re: How can I optimize this query?
Posted by: Martin Schiff
Date: March 16, 2010 01:59PM

Well, I still have not been able to do this with a single query, but I did find a solution that is VERY fast. I can lookup 1200 ip addresses and sort and subtotal them by city in about a second.

First I query to get unique IP addresses

SELECT distinct longip FROM whyu.stats

then using mysql_fetch_array on the above result, in a PHP loop, I call this query:

SELECT cl.country,cl.region,cl.city
       FROM geoipdb.CityLocation cl 
       JOIN geoipdb.CityBlocks cb ON (cl.locId=cb.locId)
       WHERE cb.endIpNum >= $longip 
       ORDER BY cb.endIpNum LIMIT 1

for each IP and add the location to an array. Then I sort the array and loop through it subtotaling by location and output that subtotal with the location.

I would still like to be able to do this in a single query, but I'm not sure it's possible to get anything close to this performance.

-- Martin

Options: ReplyQuote


Subject
Views
Written By
Posted
5244
March 12, 2010 02:40PM
2319
March 13, 2010 04:07PM
1747
March 13, 2010 04:17PM
1913
March 16, 2010 08:22AM
2377
April 14, 2010 02:27AM
Re: How can I optimize this query?
1840
March 16, 2010 01:59PM
1838
March 27, 2010 03:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.