Index (a,b,c) helps WHERE a / a,b / a,b,c — not just b
Most Selective First
Put highest-cardinality column first
Equality Before Range
(status, created_at) better than (created_at, status) for status=x AND created_at>y
Sort Order
Match ORDER BY direction to avoid sort step
Example: Composite Index
CREATE INDEX idx_orders_cust_date ON orders(customer_id, placed_at DESC) INCLUDE (total); -- covering column
4. Using Covering Indexes
Feature
Behavior
Covering Index
All query columns in index → no heap lookup
INCLUDE clause
Add non-key columns to leaf pages
Index-only scan
EXPLAIN shows "Index Only Scan"
Cost
Larger index, slower updates
5. Creating Partial Indexes
Use
Example
Skip nulls
CREATE INDEX ... WHERE col IS NOT NULL
Active rows only
WHERE deleted_at IS NULL
Rare values
WHERE status = 'pending'
Benefit
Smaller index, faster scans, lower write overhead
Example: Partial Unique Index
-- Unique only for active usersCREATE UNIQUE INDEX uq_user_email_active ON users(email) WHERE deleted_at IS NULL;
6. Implementing Full-Text Indexes
DB
Mechanism
Postgres
tsvector + GIN; to_tsquery
MySQL
FULLTEXT INDEX; MATCH ... AGAINST
SQL Server
Full-Text Search service + CONTAINS
Beyond DB
Elasticsearch, OpenSearch, Meilisearch for advanced
Example: Postgres FTS
ALTER TABLE articles ADD COLUMN tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;CREATE INDEX idx_articles_fts ON articles USING GIN(tsv);SELECT id FROM articles WHERE tsv @@ websearch_to_tsquery('english','database design');
7. Analyzing Index Usage
Tool
Query/Command
Postgres usage stats
SELECT * FROM pg_stat_user_indexes;
Postgres unused
WHERE idx_scan = 0
MySQL usage
SELECT * FROM sys.schema_unused_indexes;
Plan inspection
EXPLAIN (ANALYZE, BUFFERS) ...
Missing index
SQL Server DMV sys.dm_db_missing_index_*
8. Dropping Unused Indexes
Step
Action
Identify
idx_scan = 0 over weeks of representative traffic
Confirm not used by unique constraint
Check pg_constraint / information_schema
Drop concurrently
DROP INDEX CONCURRENTLY idx_name;
Monitor regressions
Watch slow query log after drop
9. Managing Index Maintenance
Operation
Why
REINDEX (Postgres)
Rebuild bloated indexes
ANALYZE
Update planner statistics
VACUUM
Reclaim dead tuple space
REORGANIZE / REBUILD (SQL Server)
Defrag indexes
Optimize TABLE (MySQL)
Rebuild + reclaim space
10. Balancing Index Overhead vs Query Performance
Trade-off
Effect
More indexes
Faster reads, slower INSERT/UPDATE/DELETE, more disk
Wider indexes
Better covering, larger leaf pages
Partial indexes
Smaller, less write overhead
Index-only scans
Best read perf, demand vacuum for visibility map
Note: Every index roughly doubles write cost on its column. Audit indexes quarterly.