Re: Slow JOIN to convert IP to Country Code
Replacing
KEY `dateline` (`dateline`),
with
KEY (dateline, ip_interval)
would lead to "Using index", which would speed that query up somewhat.
You cannot avoid the "filesort" since the ORDER BY is not part of an index. It is a common myth that filesort means terrible performance. You your case, the temp table is probably a MEMORY table and the "filesort" is actually a qsort call from C.
The 15 seconds comes from having to wade through a million(?) rows. Keeping a summary table of daily (hourly?) subtotals would probably speed up your query by another 10x, maybe more.
If the slowlog is turned on, look in it; you will see "Rows examined".
Subject
Views
Written By
Posted
5311
May 19, 2009 05:05AM
2393
May 19, 2009 07:11AM
2210
May 19, 2009 11:16PM
2244
May 21, 2009 01:34AM
2188
May 27, 2009 11:58AM
2164
May 27, 2009 08:05PM
2112
May 27, 2009 10:13PM
2310
May 27, 2009 07:04AM
Re: Slow JOIN to convert IP to Country Code
2066
May 27, 2009 09:46AM
2080
May 27, 2009 10:05PM
2164
May 29, 2009 10:55AM
2111
May 29, 2009 03:34PM
2417
May 30, 2009 01:43AM
2278
May 30, 2009 10:47AM
2231
May 31, 2009 11:10PM
2587
June 01, 2009 04:22AM
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.