Want your queries to go from slow 🚶♂️ to fast 🚀? Indexes are your secret weapon.
What Is an Index in SQL?
An index is like the index in a book. Instead of flipping through every page to find a word, you just jump to the right section instantly.
In a database, an index helps SQL find rows faster — especially when you’re using WHERE, JOIN, or ORDER BY.
CREATE INDEX Syntax
CREATE INDEX index_name
ON table_name (column_name);
Example Table: customers
| customer_id | name | |
|---|---|---|
| 1 | Alice | alice@email.com |
| 2 | Bob | bob@email.com |
| 3 | Charlie | charlie@email.com |
We often run:
SELECT * FROM customers WHERE email = 'bob@email.com';
Without an index: SQL checks every row (slow 🐢)
Creating the Index
CREATE INDEX idx_email ON customers(email);
Now this query runs fast because SQL jumps straight to the right row.
Before vs After Using Index
| Query Type | With Index | Without Index |
|---|---|---|
| Lookup by email | Fast | Slow |
| Insert new row | Slightly slower | Faster |
| Other columns | No change | No change |
DROP INDEX – Remove the Index
DROP INDEX idx_email;
Now SQL stops using the index and scans the table again. Queries may slow down.
When to Use Indexes
- Good for:
- Columns used in
WHERE JOINkeysORDER BYcolumns
- Columns used in
- Avoid if:
- The column is updated constantly
- The table is tiny
Recap
| Command | What It Does |
|---|---|
CREATE INDEX | Adds a fast lookup path for a column |
DROP INDEX | Deletes the index, back to full scan mode |
Final Thoughts
Indexes are like shortcuts — they make reads fast, but come at a small cost for inserts/updates.
Use them smartly on frequently searched columns, and your SQL will run like a dream