map my ip logs to countries

liveaday

i have a table listing ip ranges in numeric format and corresponding country:
ip_from | ip_to | country
355457575 | 356343334 | usa
255333444 | 256333444 | uk
....

i have another table logging the hits to my site:
hit_id | ip | date
1 | 255.33.222.10 | 20030405
2 | 128.10.100.10 | 20030406
....

i need to derive the percentage of hits for each country but i m not sure how to do a join on my tables to get something like:
hit_id | ip | country
1 | 255.33.222.10 | usa
2 | 128.10.100.10 | japan

any ideas? i can convert the numeric ip to ip format but how exactly do i join the tables? or is there a different solution?

😕

ultraslacker

You will need to convert to numeric for second query.

SELECT COUNT(*) AS total_hits
FROM ip_hits

SELECT COUNT(*) AS country_hits, country
FROM ip_hits
JOIN ip_country
ON (ip >= ip_from AND ip <= ip_to)
GROUP BY country

Do not use BETWEEN if you are running 3.23x

liveaday

your code worked when i added a left infront of the JOIN. This is what i have now and it works ok...not sure if the conversion i am making is the best way, seems to be a bit long...any ideas of making this shorter/less complex? 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

liveaday

actually the above sql will only work with small test tables. when i run it on my actual large tables it times out. i guess its slow cause all the substring functions for the ip conversion from string to numeric...how can i avoid that and refine my sql query? thanks