Indexes Aren’t Simple — They’re Strategic

𝙄 𝙪𝙨𝙚𝙙 𝙩𝙤 𝙩𝙝𝙞𝙣𝙠 𝙞𝙣𝙙𝙚𝙭𝙚𝙨 𝙞𝙣 𝙙𝙖𝙩𝙖𝙗𝙖𝙨𝙚𝙨 𝙬𝙚𝙧𝙚 𝙨𝙞𝙢𝙥𝙡𝙚: 👉 "𝙏𝙝𝙚𝙮 𝙞𝙢𝙥𝙧𝙤𝙫𝙚 𝙧𝙚𝙖𝙙 𝙥𝙚𝙧𝙛𝙤𝙧𝙢𝙖𝙣𝙘𝙚" 👉 "𝙏𝙤𝙤 𝙢𝙖𝙣𝙮 𝙞𝙣𝙙𝙚𝙭𝙚𝙨 𝙨𝙡𝙤𝙬 𝙙𝙤𝙬𝙣 𝙬𝙧𝙞𝙩𝙚𝙨" That’s it… or so I thought. While working on a recent feature, I realized there’s much more depth to how indexes actually work — especially how you define them. One key thing I learned from my lead is that: The order of columns in an index matters A LOT.

Example:

CREATE INDEX idx_abc ON table(col_a, col_b, col_c); This works efficiently for:

✔ WHERE col_a = ? ✔ WHERE col_a = ? AND col_b = ? ✔ WHERE col_a = ? AND col_b = ? AND col_c = ?

But surprisingly:

❌ WHERE col_b = ? ❌ WHERE col_c = ?

👉 Because indexes follow a left-to-right (prefix) rule.

---

Another mistake I used to overlook:

If most queries are like: WHERE col_a = ? AND col_b = ?

But your index is: (col_z, col_a, col_b)

👉 That index might not be useful at all.

---

💡 Big takeaway for me:

Indexes are not just about adding them. They’re about designing them based on query patterns.

• What columns are filtered most?
• In what order are they used?
• How selective are they?

---

Also learned: Every index comes with a tradeoff. More indexes = ⬆ Faster reads ⬇ Slower writes (INSERT / UPDATE / DELETE need to maintain them)

Still learning, but this completely changed how I think about performance.