Improving Query Performance by 10000x

medium.com

2 pointsnraf2 comments

These "we learned to use the database properly and now the code is much faster"-articles which crop up every now again are fascinating, and speaks a lot about how poorly understood databases are among developers.

Granted, it's not until you have a decent amount of data in the database you actually get a feel for what is fast and what is not, but this kinda does feel like things you should probably know before setting up your database model.

Author here. It's not a question of not understanding how databases work, but as a startup the focus is on moving quickly, not spending time optimising queries before we've achieved traction.

The table with index was setup 3+ years ago and has served us well until now, where we noticed slowdown for particular types of data.

The article is an exercise in diagnosing an issue with db performance and systemically working through it to the right solution.

In this case, understanding why the index wasn't effective, highlighting that column order matters for multicolumn indexes, and how big an impact having the right column can make.

Reply on news.ycombinator.com