Database indexes-speed up queries dramatically

A database index is like a book's index - it lets you quickly find data instead of reading every row. Queries with proper indexes return results in milliseconds instead of seconds.

How indexes work

• Without index: MySQL scans every row (slow for large tables)
• With index: MySQL uses B-tree structure for fast lookups (very fast)
• Trade-off: Indexes speed up SELECT but slow down INSERT/UPDATE (must update index too)

Index types

PRIMARY KEY

Unique identifier for each row. Only one per table:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);

UNIQUE INDEX

Ensures all values are unique:

CREATE UNIQUE INDEX idx_email ON users(email);

Composite Index

Index on multiple columns for complex queries:

CREATE INDEX idx_user_status ON posts(user_id, status);

Create and drop indexes

-- Create
CREATE INDEX idx_name ON table_name(column_name);

-- Drop
DROP INDEX idx_name ON table_name;

When to add indexes

• Columns in WHERE clause: Frequently filtered columns need indexes
• JOIN columns: Foreign keys used in JOINs
• ORDER BY columns: Sorting on indexed columns is faster
• Not for small tables: Skip indexes on tiny tables (<1000 rows)
• Not for low-cardinality: Skip indexes on columns with few unique values (like gender: M/F)

Find missing indexes

Use EXPLAIN to see if a query uses indexes:

EXPLAIN SELECT * FROM posts WHERE user_id = 5;
-- If "rows" is very high and "key" is NULL, add an index on user_id

Related: SQL basics | Slow query analysis | MySQL tuning

Related articles