From 17ms to 0.04ms: How to Design the Right SQL Index

This article explains how to design efficient SQL indexes by focusing on the queries your application runs rather than the table schema. It covers the basics of B-tree indexes, the importance of column order in composite indexes (equality columns first, then sort/range columns), and how to use EXPLAIN ANALYZE to verify index effectiveness. A practical example with a Postgres database containing 1 million comments demonstrates how the right index can reduce query time from 17ms to 0.04ms. The article also touches on index-only scans and provides a step-by-step approach to analyzing query plans and creating optimal indexes.