MySQL Forums :: Optimizer & Parser :: How can I optimize this query?


Advanced Search

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

Options: ReplyQuote


Subject Views Written By Posted
How can I optimize this query? 4235 Martin Schiff 03/12/2010 02:40PM
Re: How can I optimize this query? 2027 Rick James 03/13/2010 04:07PM
Re: IP range optimization 1545 Rick James 03/13/2010 04:17PM
Re: IP range optimization 1628 Martin Schiff 03/16/2010 08:22AM
Re: IP range optimization 2006 pavel iliev 04/14/2010 02:27AM
Re: How can I optimize this query? 1665 Martin Schiff 03/16/2010 07:58AM
Re: How can I optimize this query? 1547 Martin Schiff 03/16/2010 08:16AM
Re: How can I optimize this query? 1605 Martin Schiff 03/16/2010 08:21AM
Re: How can I optimize this query? 1579 Martin Schiff 03/16/2010 08:16AM
Re: How can I optimize this query? 1575 Martin Schiff 03/16/2010 09:40AM
Re: How can I optimize this query? 1611 Martin Schiff 03/16/2010 01:59PM
Re: How can I optimize this query? 1607 Rick James 03/27/2010 03:36PM
Re: How can I optimize this query? 1434 Martin Schiff 04/14/2010 07:21AM


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.