How can I optimize this query?
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.
Subject
Views
Written By
Posted
How can I optimize this query?
5538
March 12, 2010 02:40PM
2424
March 13, 2010 04:07PM
1847
March 13, 2010 04:17PM
2005
March 16, 2010 08:22AM
2517
April 14, 2010 02:27AM
2013
March 16, 2010 07:58AM
1921
March 16, 2010 08:16AM
1925
March 16, 2010 08:21AM
1934
March 16, 2010 08:16AM
1926
March 16, 2010 09:40AM
1964
March 16, 2010 01:59PM
1959
March 27, 2010 03:36PM
1708
April 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.