Indexing Best Practices
B-Tree Indexes
-- Create an index
CREATE INDEX idx_last_name ON users(last_name);
-- Composite index
CREATE INDEX idx_name_email ON users(last_name, email);Full-Text Search
-- Create a full-text index (MySQL syntax)
CREATE FULLTEXT INDEX idx_content
ON articles(title, content);Query Optimization
Before Optimization
-- SELECT * fetches every column, and wrapping the column
-- in YEAR() stops an index on created_at from being used
SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE YEAR(o.created_at) = 2024;After Optimization
-- Only the needed columns, and a range the index can use
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
AND o.created_at < '2025-01-01';
