SQL – Indexes

An index is a separate data structure that the database maintains alongside a table to speed up lookups. Think of a book’s index at the back — instead of reading every page to find “photosynthesis,” you jump directly to page 247. Database indexes work the same way, allowing the engine to find rows matching a WHERE condition without scanning every row.

The trade-off: indexes speed up reads but slightly slow down writes (INSERT, UPDATE, DELETE) because the index must be updated along with the table. Index the columns you query most frequently; leave rarely-queried columns unindexed.

When to create an index ✅ Columns used in WHERE conditions ✅ Columns used in JOIN ON clauses ✅ Columns used in ORDER BY ❌ Columns rarely filtered on ❌ Very small tables (full scan is fine) ❌ Columns with very low cardinality (e.g., boolean)

Try It Yourself — Interactive SQL Editor

Edit the query below and click Run Query ▶ to see live results powered by SQLite running directly in your browser.

Key Points

• Indexes dramatically reduce query time on large tables
• Created separately from the table with CREATE INDEX
• Automatically used by the query optimizer — no query changes needed
• Too many indexes slow down writes — be selective

Pro Tip from CodesCompiler: The best way to learn SQL is to break things intentionally — modify the query above, change the WHERE conditions, try different columns. Every error teaches you something the docs cannot.

In the next lesson, we continue exploring SQL’s powerful feature set to build your database mastery.