code everywhere
technology, web services and applications

improving database performance

posted on July 14, 2016, 6:50 pm in php, sql

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;
bash -- 70x32
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.

bash -- 70x32
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 our test again on 1000 IPs we get 0.06 seconds.
bash -- 70x32
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.

recent posts

< back