Indexes
Advanced⏱️ 17 mins read
What You'll Learn
An index is a data structure that speeds up reads by letting the DB find rows without scanning the full table — like a book index. Clustered Index: physically reorders the table data (one per table, usually the Primary Key). Non-Clustered Index: separate structure with pointers to rows (multiple allowed). Indexes speed up reads but slow down writes.
Syntax
CREATE INDEX idx_name ON table(col);
CREATE UNIQUE INDEX idx ON table(col);
CREATE INDEX idx ON table(col1, col2);
DROP INDEX idx_name ON table;
EXPLAIN SELECT ...;Example
-- Index on frequently filtered column
CREATE INDEX idx_email ON users(email);
-- Composite index for common query pattern
CREATE INDEX idx_cust_date
ON orders(customer_id, order_date);
-- Check if query uses the index
EXPLAIN SELECT * FROM users
WHERE email = 'test@example.com';
-- Look for 'type: ref' and 'key: idx_email'Common Mistakes
Over-indexing: every index slows down INSERT/UPDATE/DELETE because the index must also be updated. Don't index low-cardinality columns (e.g., boolean, gender). Applying functions to indexed columns in WHERE (YEAR(date), LOWER(email)) disables the index.
Interview Tips
Index columns used in WHERE, JOIN ON, and ORDER BY with high cardinality. Composite indexes: column order matters — (customer_id, date) serves queries on customer_id alone but NOT queries on date alone. Explain EXPLAIN output: type=ALL = full scan (bad), type=ref = index used (good).
Practice
You have a 10M-row orders table. Queries filter by customer_id and order_date. What index would you create and why? What would EXPLAIN show before vs after?