I use the following sql which works ok with small tables but too slow, unworkable with real size tables. the problem is with the converting of the ip to numeric format...how could i make this sql more efficient, faster? note that ip_to and ip_from are in numeric format while ip is a string (xxx.xxx.xxx.xxx) thanks
SELECT COUNT() AS country_hits, country
FROM ip_hits
left JOIN ip_country
ON (
SUBSTRING_INDEX(ip,'.',1) 256 256 256 + SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) 256 256 + SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',3),'.',-1) 256 + SUBSTRING_INDEX(ip,'.',-1) >= ip_from
AND
SUBSTRING_INDEX(ip,'.',1) 256 256 256 + SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',2),'.',-1) 256 256 + SUBSTRING_INDEX(SUBSTRING_INDEX(ip,'.',3),'.',-1) * 256 + SUBSTRING_INDEX(ip,'.',-1) <= ip_to
)
GROUP BY country
