improving database performance
Writing efficient code is always important. A great example is the IP lookup code in our geoip-wrapper ( github.com/codeeverywhereca/geoip-wrapper ).
The way the wrapper works, we created a database with the CSV files and then can run SQL queries to find locations.
first attempt
The original lookup on 1000 IPs took ~383 seconds, not great. Heres the code:
select * from GeoLite2_City_Blocks_IPv4 t1 inner join GeoLite2_City_Locations_en t2 on t1.geoname_id = t2.geoname_id where ? between start and end;
Running performance test on 1000 IPs Test completed, took 383.22868394852 s
second attempt
We could do better, lets add an index to our SQLite database.
create index ip4index on GeoLite2_City_Blocks_IPv4(end);
Running our test again on 1000 IPs again gives us 15 seconds, much better.
Running performance test on 1000 IPs Test completed, took 15.238310098648 s
third attempt
Maybe we could do better, doing a between lookup is good but since our column is ordered, we only really need to look up to an address.
Lets change the query a little.
select * from GeoLite2_City_Blocks_IPv4 t1 inner join GeoLite2_City_Locations_en t2 on t1.geoname_id = t2.geoname_id where ? <= end limit 1;
Running performance test on 1000 IPs Test competed, took 0.061554908752441 s
the wrap up
Now thats an improvement, from 383 to 0.06 a reduction of ~6000%.
Before writing your code, always take a minute to step back and think about the design before blindly pushing out some code.