MySQL Forums
Forum List  »  Optimizer & Parser

How can I optimize this query?
Posted by: Martin Schiff
Date: March 12, 2010 02:40PM

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.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.

Options: ReplyQuote

Written By
How can I optimize this query?
March 12, 2010 02:40PM
March 13, 2010 04:07PM
March 13, 2010 04:17PM
March 16, 2010 08:22AM
April 14, 2010 02:27AM
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.