Avoid these 5 query patterns to optimise API performance

When I set out to improve the performance of some time-consuming APIs, I found that nothing was wrong with the application logic. Instead, it was the database queries taking time (one way to spot this is through New Relic metrics). Inefficient queries can cause slow responses, higher resource usage, and possible database overload, significantly impacting API performance and user experience.

Here are five commonly made querying mistakes that hurt performance, with the time comparisons I measured. The dataset: an item table, a user table, and a transaction table, each with 1M rows.

1. Not using bulk operations

Consider fetching the prices of a list of items. Fetching them one by one in a loop took about 2.2 ms for 50 items, while a single bulk fetch with an WHERE id IN (...) took just 0.163 ms. The same holds for bulk updates.

2. Not using joins

Fetching a user and then their transactions in two separate queries took 36.3 ms total (17.5 + 18.8). A single query using an inner join on user.id = transaction.user_id took 27.1 ms. The join is clearly shorter.

3. Fetch and then update

Fetching a row, changing a field, then saving took 0.53 ms. A direct UPDATE ... WHERE id = ? took 0.4 ms. The difference is small, but the direct update avoids a round trip.

4. Not using pagination

Returning a large result set without pagination increases network latency and response time. Fetching all rows took 12 ms, while fetching one page took 0.1 ms. The total time to walk every row is higher when paginated, but any single page is far faster, which is what the user actually waits on.

5. Wrong indexing

Without proper indexing, the database can fall back to full table scans and slow query execution. But wrong indexing is just as bad: it doesn't speed anything up, it adds storage, and it isn't useful. I have seen tables indexed on a date-time field where the query filters on a wide range. The index storage size becomes huge and provides no benefit unless the query range is small. If you've noticed other bad query patterns in your apps, I'd love to hear them.