Ideaticus.com

Many web applications use ip2location for ads targeting, restrict content to a specific location or just because they need to know where the traffic comes from. The initial setup of ip2location database is very slow. Important seconds are lost because ip2location and because the programmer will write the query like this:

SELECT countrySHORT FROM ip2location WHERE ‘.$n_ip.’ BETWEEN ipFROM AND ipTO

This query is very bad because of its highly execution time. Let’s try a different approach:

SELECT countrySHORT FROM ip2location WHERE ‘.$n_ip.’ <= ipTO LIMIT 1

The second query will use the index for column ipTO which will speed up the query a lot. The default setup of ip2location doesn’t have indexes on ipFROM and ipTO, which it means you have to create them by your self using the following syntax:

CREATE UNIQUE INDEX ipTO ON ip2location (ipTO)

Conclusion: working with large databases is not an easy task but tricks like one presented above will help you a lot.

Trackback URI | Comments RSS