Re: Slow JOIN to convert IP to Country Code

[Harutyun Drnoyan]

Hello.

I've followed your suggestion and moved code column to stats table. That seems to solve my problam. Now query looks like

SELECT
  stat.ip_code,
  COUNT(*)     AS COUNT,
  (SELECT
     NAME
   FROM codes
   WHERE CODE = stat.ip_code) AS NAME
FROM stats AS stat
WHERE stat.dateline >= 1243814400
    AND stat.dateline < 1243900800
GROUP BY stat.ip_code
ORDER BY COUNT DESC
LIMIT 0, 10

where codes is the table which stores mapping of country codes to full names. The subquery uses eq_ref type and scans only 1 row, the main query uses range scan and index. And this query takes only ~0.7 second on same test table (~10M rows). That's great result I think.

Thank you for your help.