Painless geolocation
Each site built with Edicy can have multiple language environments and when visitors land on the front page of the site, we'd like to display it in the language that the user understands the most. Of course, we're looking at browser accept_language property but this can be a problem since many users do not know that they can set this property and leave it as default (usually english).
This is where geolocation comes handy - if accept_language property is set to english, we take extra step to find visitors location by ip-address. And by knowing the country the user is coming from we can assume what is the most suitable language on site.
We're using free GeoLiteCity database by MaxMind. Based on the data provided in this database it is easy to go in the wrong direction depending how do you query data. In fact, we did go in the wrong direction and used very inefficient queries to get location information from the database. It did not bother us much but traffic in our sites have been growing rapidly and that also means more requests to geoip database.
Fortunately i stumbled onto Jeremy Cole's article on how to use GIS features in MySQL database to query from MaxMind's database more efficiently. Very interesting reading. And by simply changing the few lines in code we were able to decrease load to almost nothing in our database server. Here is the graph that very clearly shows the effect of this change:
And here is the graph from New Relic RPM (yes there is time difference on these graphs because New Relic displays the time accordingly to our local time zone):
In conclusion, this was a very big win!