. . .

[ Ajouter un commentaire ] [ 4 commentaires ]
# Posté le vendredi 04 janvier 2008 10:44
Modifié le vendredi 04 janvier 2008 15:25

Paris Web 2007, Nouveau prof (de) Mat à l'INSIA

Paris Web 2007, Nouveau prof (de) Mat à l'INSIA
[ Ajouter un commentaire ] [ 13 commentaires ]
# Posté le dimanche 18 novembre 2007 14:56
Modifié le dimanche 18 novembre 2007 15:14

Prague

Prague
[ Ajouter un commentaire ] [ 5 commentaires ]
# Posté le dimanche 11 novembre 2007 18:23

Ptit post depuis Prague

Ptit post depuis Prague
Ptit post depuis Prague ou nous avons passe un ptit week end sympa, mais un peu humide :/
Hier pluie, bof, ce matin neige, mieux :p ou pas

Ambiance mouvementee dans le centre ville (voir photo)... mais sinon c est tres beau :)
[ Ajouter un commentaire ] [ 1 commentaire ]
# Posté le dimanche 11 novembre 2007 03:16
Modifié le dimanche 11 novembre 2007 18:13

MySQL udf geoip country : get the country name/code for a hostname or an IP address inside MySQL

A co-worker ask me this week to try to determine if we have users who are using anonymous proxy (and perhaps sending spam on our services...), so i decided to analyse one day of connections logs from our platform using GeoIP.

GeoIP can tell me for a ip adress if it's an anonymous proxy by returning A1 when asking for the country code.

This logs are inside a mysql database and i wanted to stay inside mysql in order to do some crazy queries like "give me accounts who were connected from different country this day ordered by the number of different country descending.".

This lasts days i got some interest in mysql udf function, so i wanted to write this udf to get country code from ip adress inside mysql, without importing the geoip database inside mysql and do some join...

Here is the GeoIP Country UDF for MySQL : udf_geoip_country-1.0.tar.gz
mysql> select get_country_code3_by_addr('212.129.63.1');
FRA
mysql> select get_country_name_by_name('www.skyrock.com');
France
mysql> select count(*) from my_test_log where get_country_code_by_addr(ip) != '';
+----------+
| count(*) |
+----------+
|  9470127 |
+----------+
1 row in set (14.45 sec)


i think 650000 resolved ip adress per second is a good performance, and i'm not sure a join could have done better for this job. (sorry i didnt try)
[ Ajouter un commentaire ] [ 1 commentaire ]
# Posté le mercredi 17 octobre 2007 18:28
Modifié le lundi 05 novembre 2007 09:16