Faster is better – rethinking SQL queries to improve API response times
At the moment we’re redeveloping an existing JSON based API, which is used by a mobile application.
We knew the performance of the previous release of the API was good – it’s been in use for the last two years without any problems, and easily outperforms a competitor’s API. However, our dataset size is due to more than double soon and hopefully the traffic/popularity of the app will increase as it becomes more useful to end users.
Our testing showed that what was an acceptable request time on the old API (in edge cases ~5 seconds before results would be returned) could now take 10-15 seconds – due to the larger result set – which isn’t ideal – and would drastically affect the capacity and usability of the service.
Profiling the MySQL database queries led us to some minor optimisations, but what really helped in this instance, was to remove logic from the database layer into the application layer and to pre-load the cache. So, the database query time reduced – but not by enough – as cache misses would still take ~8-10 seconds.
So, our next step was to try and reduce the number of cache misses by pre-loading the cache – given that we can predict some of the common requests. So while those requests still take ~10 seconds, they can be scheduled to run in the background periodically to ensure more end users receive cache hits – and near instant replies taking < 0.2 seconds.