<h2>What Are Indexes?</h2><p>A database index is a data structure that <strong>speeds up data retrieval</strong> at the cost of additional storage and slower writes. Think of it like a book's index — instead of scanning every page, you jump directly to the right one.</p><h3>When to Add an Index</h3><ul><li>Columns used frequently in <code>WHERE</code> clauses</li><li>Foreign key columns used in <code>JOIN</code> operations</li><li>Columns used in <code>ORDER BY</code> or <code>GROUP BY</code></li></ul><h3>When NOT to Index</h3><ul><li>Tables with very few rows</li><li>Columns with low cardinality (e.g., boolean flags)</li><li>Tables that are write-heavy with rare reads</li></ul><h3>Composite Indexes</h3><p>A composite index covers <em>multiple columns</em>. The column order matters:</p><pre><code>CREATE INDEX idx_user_status ON orders (user_id, status);</code></pre><blockquote><p>The "leftmost prefix" rule means this index helps queries filtering by <code>user_id</code> alone, or <code>user_id</code> + <code>status</code>, but <strong>not</strong> <code>status</code> alone.</p></blockquote><hr><p>Run <code>EXPLAIN</code> on your slow queries to verify indexes are being used effectively.</p>