Indexing Strategies for Faster Database Queries

TL;DR Indexes = direct lookups — milliseconds vs full table scans (seconds). B-tree for most queries — Supports = , , > , BETWEEN , LIKE 'prefix%' , ORDER BY . Index WHERE / JOIN / ORDER BY columns — Otherwise full scan. Composite index order matters — (a, b, c) works for a , a+b , a+b+c — not b or c alone. Equality first, then range. Partial indexes — WHERE active = true = smaller, faster. Covering indexes with INCLUDE — Query never touches table. Maintenance — Drop unused ( idx_scan = 0 ), rem