How to Create and Use Indexes in MySQL

Indexes are data structures that MySQL maintains alongside your tables to speed up data retrieval. Without an index, MySQL must scan every row in a table (a full table scan) to find matching records. With the right indexes, queries that once took seconds can return in milliseconds.

Creating an Index

Use CREATE INDEX to add an index to an existing table. You can also define indexes inline when creating a table. Primary keys are automatically indexed.

-- Index on a single column
CREATE INDEX idx_employees_email
ON employees (email);

-- Index on multiple columns (composite)
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

Unique Indexes

A unique index enforces that no two rows can have the same value in the indexed column(s). This is both a constraint and a performance optimization.

CREATE UNIQUE INDEX idx_users_username
ON users (username);

Composite Index Order Matters

In a composite index, column order is critical. MySQL can use the index for queries that filter on the leftmost columns. An index on (customer_id, order_date) helps queries filtering by customer_id alone or by both columns, but not by order_date alone.

-- Uses the composite index (leftmost prefix)
SELECT * FROM orders WHERE customer_id = 42;

-- Uses the full composite index
SELECT * FROM orders
WHERE customer_id = 42 AND order_date > '2025-01-01';

-- Cannot use the index (missing leftmost column)
SELECT * FROM orders WHERE order_date > '2025-01-01';

Full-Text Indexes

For searching natural language text in VARCHAR or TEXT columns, full-text indexes outperform LIKE with wildcards. Use MATCH ... AGAINST to query them.

ALTER TABLE articles
ADD FULLTEXT INDEX idx_articles_content (title, body);

SELECT title, body
FROM articles
WHERE MATCH(title, body) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

Viewing and Dropping Indexes

Use SHOW INDEX to inspect existing indexes on a table. Drop unused indexes to reduce write overhead.

SHOW INDEX FROM employees;

DROP INDEX idx_employees_email ON employees;

When to Index

Index columns that appear in WHERE, JOIN, and ORDER BY clauses. Avoid indexing columns with very low cardinality (like boolean flags) or tables that are mostly written to and rarely read. Every index adds overhead to INSERT, UPDATE, and DELETE operations because MySQL must update the index alongside the data. Use EXPLAIN to verify that your indexes are actually being used.