Indexes

Indexes are data structures that improve query performance. This chapter introduces how to create and use indexes.

Create Index

-- Single column index
CREATE INDEX idx_name ON users(name);

-- Composite index
CREATE INDEX idx_city_age ON users(city, age);

-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);

Drop Index

-- Drop index
DROP INDEX idx_name ON users;

-- MySQL syntax
ALTER TABLE users DROP INDEX idx_name;

Index Types

1. B-Tree Index (default) - For most queries
2. Hash Index - For equality queries
3. Full-text Index - For text search
4. Spatial Index - For geographic data

When to Use Indexes

Should create index:

• Columns in WHERE clause
• JOIN condition columns
• ORDER BY columns
• Frequently queried columns

Should not create index:

• Small tables
• Frequently updated columns
• Low cardinality columns (like gender)

View Indexes

-- View table indexes
SHOW INDEX FROM users;

-- View index usage
EXPLAIN SELECT * FROM users WHERE name = 'Zhang San';

Performance Optimization

-- Use covering index
CREATE INDEX idx_name_age ON users(name, age);
SELECT name, age FROM users WHERE name = 'Zhang San';

-- Prefix index
CREATE INDEX idx_email_prefix ON users(email(10));

Summary

• Indexes improve query speed
• But slow down writes
• Use indexes wisely
• Maintain indexes regularly

Next Step: Learn TRANSACTIONS