What is an Index?
An Index makes searching data much faster - like a book index!
Simple rule: Index = Quick lookup instead of scanning everything
How Index Works
Index Syntax
-- Create index
CREATE INDEX index_name ON table(column);
-- Create index on multiple columns
CREATE INDEX index_name ON table(col1, col2);
-- Drop index
DROP INDEX index_name;When to Create Index
YES - Create index on:
- Columns in WHERE clauses (searched often)
- Columns in JOIN conditions
- Columns in ORDER BY
NO - Don't create index on:
- Small tables (< 1000 rows)
- Columns rarely used in searches
- Columns with few unique values (Yes/No, M/F)
Index Types
- Single Column - Index on one column
- Composite - Index on multiple columns together
- Unique - Index that enforces uniqueness
Trade-offs
Pros:
- SELECT queries become faster
- JOIN operations speed up
- ORDER BY is quicker
Cons:
- INSERT/UPDATE/DELETE become slower
- Takes extra storage space
- Too many indexes = slower writes
Best Practices
- Index columns you search often
- Don't over-index - only what you need
- Composite indexes - order matters (most selective first)
- Monitor performance - remove unused indexes
Summary
- Index = Fast lookups
- Create on frequently searched columns
- Avoid on small tables or rarely-used columns
- Balance: Fast reads vs slower writes