I got a log table with IP's of all visitors.
In one diagram I need to calculate the top 5 visiting countries based on the complete log table.
SELECT mIPC.pCOUNTRY, COUNT(mIPC.pCOUNTRY) as tempCount
FROM mIPC
INNER JOIN mStatistics ON mIPC.pRange_Start <= INET_ATON(mStatistics.pAddr) AND mIPC.pRange_End >= INET_ATON(mStatistics.pAddr)
GROUP BY mIPC.pCOUNTRY
ORDER BY tempCount DESC
LIMIT 0,5
Currently the mIPC table has about 84.000 rows containing the ip ranges and countries.
If every row from my log table has to check its IP against the 84.000 rows of the "country" table then sure I realize it takes time.
Maybe the above SQL could be written differently to speed up the process.
Any ideas?
