Re: How can I optimize this query?

[Martin Schiff]

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