Database Indexes Explained (Speed Up Queries)

When your table grows, queries that were fast can become slow. An index is like a book’s table of contents: - without index: MySQL scans many rows - with index: MySQL jumps directly to matching rows ## Simple example If you often run: - `SELECT * FROM users WHERE email = ?` Then you want an index on email. ```sql CREATE INDEX idx_users_email ON users(email); ``` ## Important tradeoff Indexes: ✅ speed up reads ❌ slow down writes (INSERT/UPDATE) a bit, because the index must be updated ## When to add an index - columns used in WHERE - columns used in JOIN - columns used in ORDER BY (sometimes) ## Visual idea ```mermaid flowchart LR A[Query] --> B{Index exists?} B -->|Yes| C[Fast lookup] B -->|No| D[Full table scan] ``` > Next: Pagination, show results page-by-page without loading everything.