A missing ORDER BY cost us Thousands in API calls

In 2022, I fixed one of the most expensive one-line bugs we've shipped.

The fix was this:

- Area.all()
+ Area.query().orderBy('id')

That's it.

A missing SQL ORDER BY was causing identical requests to generate different Redis cache keys.

The application worked. Redis worked. MySQL worked.
But our Google Maps API bill had climbed to around €3,500 per year.

The context

I'm the co-founder and CTO of MyCater, a B2B catering marketplace.

One of the things our backend needs to determine is which caterers can deliver to a customer's address.

These checks happen several times during the ordering process, so we had a Redis cache in front of our distance calculations to avoid repeating expensive external API calls.

The cache key was generated from the destination and the caterers addresses we wanted to check.

The old code looked roughly like this:

let serializedAreas = ''

for (const method in areasGeocodes) {
  for (const area of areasGeocodes[method]) {
    serializedAreas += JSON.stringify(area)
  }
}

const redisKey = `distance_matrix:${hash(
  JSON.stringify(destination) + serializedAreas
)}`

It looked deterministic.

Same destination + same areas = same cache key.

Except we forgot to add an ORDER BY.

SQL wasn't returning the areas in a guaranteed order

The areas originally came from:
await Area.all()

So

Area 1
Area 2
Area 3

Would give this hash: destination + 1 + 2 + 3

When you test it manually, it looks like it returns them in order but without an ORDER BY, row order isn't guaranteed.

So the hash for 3 Areas could look either like:
destination + 1 + 2 + 3
destination + 1 + 3 + 2
destination + 2 + 1 + 3
destination + 2 + 3 + 1
destination + 3 + 1 + 2
destination + 3 + 2 + 1

Giving a different string -> different hash -> different Redis key.
Generating an extra API call.

Our mistake was allowing an unordered database result to become part of something that absolutely needed to be deterministic.

The fix

The production fix was incredibly small:
await Area.query().orderBy('id')

Why this bug is interesting

Nothing crashed, no 500 errors.
MySQL returned exactly what we asked for.
Every component was working.
That's why the bug survived long enough to become expensive. And as the number of caterers grew, the problem became more costly.

At the time, our Google Maps API usage was costing us roughly €3,500 per year.

Fixing this cache issue reduced unnecessary distance API calls bringing the bill to around €100/month.