How can I optimize this query?

[Martin Schiff]

How can I optimize this query based on the Maxmind Geolocation city database? Right now the time to execute it increases exponentially, the larger the number of IP addresses. If I limit it to 20 in the subquery, it takes about 30 seconds, but if I limit to 50, it takes minutes.

All of the columns involved in joins are indexed.

The required result is a list of all cities involved with a count of the number of IP accesses from that city.

It is the join using the BETWEEN operator that is the cause of the slowness. If I eliminate the second join, there is virtually no difference in performance, and the subquery is instant on its own.

There are about 3.6 million rows in the cityblocks table.

SELECT goodips.longip,geoipdb.cityblocks.locId,geoipdb.citylocation.city,geoipdb.citylocation.region,count(*) as loccount
FROM (select distinct whyu.stats.longip from whyu.stats WHERE whyu.stats.longip!=0) as goodips
STRAIGHT_JOIN geoipdb.cityblocks on goodips.longip BETWEEN geoipdb.cityblocks.startIpNum and endIpNum
INNER JOIN geoipdb.citylocation on geoipdb.cityblocks.locId=geoipdb.citylocation.locId
GROUP BY geoipdb.cityblocks.locId

Thanks very much for any help you can provide.