How to Create Indexes in MySQL Database

If there’s one topic that separates people who can write SQL from people who can actually run a database in production, in my experience, it’s indexing. I’ve walked into more than one client project where a query took 30 seconds not because MySQL is slow, but because nobody had ever added the right index. In this guide, I’ll go deep into what indexes actually are, how MySQL uses them internally, and how I decide where to add them.

What an Index Actually Is

An index is a separate data structure that MySQL maintains alongside a table, designed to let it find rows quickly without scanning every single row. In InnoDB, indexes are implemented as B+Tree structures.

graph TD
    A[B+Tree Root Node] --> B[Internal Node]
    A --> C[Internal Node]
    B --> D[Leaf Node: Data Pointers]
    B --> E[Leaf Node: Data Pointers]
    C --> F[Leaf Node: Data Pointers]
    C --> G[Leaf Node: Data Pointers]

Every leaf node in the B+Tree is linked to its neighbors, which makes range scans (BETWEEN, >, <, ORDER BY) efficient, since MySQL can walk sequentially across leaf nodes once it finds the starting point.

Clustered vs Secondary Indexes in InnoDB

This distinction fundamentally shapes how I design tables:

• The primary key is the clustered index — the actual table data is physically stored in the leaf nodes, ordered by primary key value.
• Every secondary index stores the indexed column’s value plus the primary key value as a pointer back to the actual row. This means looking up a row through a secondary index technically requires two lookups: one in the secondary index B+Tree, then one in the clustered index to fetch the full row (unless it’s a covering index).

graph LR
    A[Secondary Index on email] -->|finds matching primary key| B[Clustered Index on id]
    B --> C[Full Row Data]

Step 1: Creating a Basic Index

CREATE INDEX idx_customer_email ON customers(email);

I check that it was created:

SHOW INDEX FROM customers;

Sample output:

+------------+------------+--------------------+--------------+-------------+
| Table      | Non_unique | Key_name           | Column_name  | Cardinality |
+------------+------------+--------------------+--------------+-------------+
| customers  | 0          | PRIMARY            | id           | 5000        |
| customers  | 0          | email              | email        | 5000        |
| customers  | 1          | idx_customer_email | email        | 5000        |
+------------+------------+--------------------+--------------+-------------+

Creating a Unique Index

CREATE UNIQUE INDEX idx_unique_sku ON products(sku);

I use unique indexes whenever a business rule requires no duplicates — SKUs, email addresses, national ID numbers — since MySQL enforces this constraint at the storage engine level, not just in application code.

Creating a Composite (Multi-Column) Index

CREATE INDEX idx_customer_status_date ON orders(customer_id, status, created_at);

This is where I’ve seen the most confusion among developers I’ve mentored, so I explain it carefully: a composite index is only useful for queries that filter on a left-prefix of the indexed columns, in order.

-- This query CAN use the composite index efficiently
SELECT * FROM orders WHERE customer_id = 5 AND status = 'pending';

-- This query CANNOT use it efficiently, since 'status' isn't the leftmost column
SELECT * FROM orders WHERE status = 'pending';

Covering Indexes

A covering index contains every column a query needs, letting MySQL satisfy the entire query directly from the index without touching the clustered index at all.

CREATE INDEX idx_covering ON orders(customer_id, status, order_total);

SELECT customer_id, status, order_total
FROM orders
WHERE customer_id = 5 AND status = 'pending';

Since every selected column exists in the index itself, EXPLAIN shows Using index under Extra, which tells me MySQL never had to look up the actual row data — a meaningful performance win on large tables.

Full-Text Indexes

For searching natural-language text, I use MySQL’s built-in full-text search rather than LIKE '%term%', which can never use a regular B+Tree index effectively:

CREATE FULLTEXT INDEX idx_ft_description ON products(description);

SELECT * FROM products
WHERE MATCH(description) AGAINST('wireless headphones' IN NATURAL LANGUAGE MODE);

Prefix Indexes

For long VARCHAR or TEXT columns, indexing the entire column can be wasteful. I often index just a prefix:

CREATE INDEX idx_prefix_description ON products(description(20));

I choose the prefix length by measuring selectivity first:

SELECT
    COUNT(DISTINCT LEFT(description, 10)) / COUNT(*) AS selectivity_10,
    COUNT(DISTINCT LEFT(description, 20)) / COUNT(*) AS selectivity_20
FROM products;

I pick the shortest prefix length that still gives me selectivity close to indexing the full column.

Using EXPLAIN to Confirm Index Usage

EXPLAIN SELECT * FROM orders WHERE customer_id = 5 AND status = 'pending';

+----+-------------+--------+------+-------------------------+-------------------------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref  | rows | Extra |
+----+-------------+--------+------+-------------------------+-------------------------+---------+------+------+-------+
| 1  | SIMPLE      | orders | ref  | idx_customer_status_date| idx_customer_status_date| 6       | const,const | 12 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+------+------+-------+

I focus on the key column (which index was actually chosen) and rows (the estimated number of rows scanned) — a low rows count relative to the table’s total size tells me the index is doing its job effectively.

A Real-World Scenario: Fixing a Slow Reporting Query

A client’s admin dashboard was taking over 8 seconds to load a filtered order list. I ran:

EXPLAIN SELECT * FROM orders
WHERE status = 'shipped' AND created_at BETWEEN '2026-06-01' AND '2026-06-30'
ORDER BY created_at DESC;

The type column showed ALL — a full table scan across 2.3 million rows. I added a composite index matching the actual filter and sort pattern:

CREATE INDEX idx_status_created ON orders(status, created_at);

After adding the index, the same query dropped from 8 seconds to under 40 milliseconds, and EXPLAIN showed type: range with rows reduced from 2.3 million to roughly 4,000 — a difference I’ve seen repeated across dozens of similar cases throughout my career.

Index Design Decision Table

Security Considerations Around Indexing

Indexing itself isn’t usually a direct security concern, but I keep a few things in mind:

• Over-indexing sensitive columns (like unhashed personal data) can increase the attack surface if the database file itself is compromised, since indexed values are stored in plaintext within the index structure just like the table.
• I ensure indexes on encrypted or tokenized sensitive columns don’t inadvertently leak information through index statistics or ordering.

Troubleshooting Common Indexing Issues

Issue: Index exists but MySQL isn’t using it

I check whether the column is wrapped in a function (WHERE YEAR(created_at) = 2026 prevents index usage; I rewrite as WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'), or whether the optimizer’s statistics are stale:

ANALYZE TABLE orders;

Issue: Too many indexes slowing down writes

Every index adds overhead to INSERT, UPDATE, and DELETE operations, since each one must be maintained in sync with the table. I periodically audit unused indexes:

SELECT * FROM sys.schema_unused_indexes;

I drop indexes that genuinely aren’t used by any query pattern, since they’re pure overhead at that point.

Issue: Duplicate or redundant indexes

SELECT * FROM sys.schema_redundant_indexes;

I’ve found this view catches surprising amounts of redundancy in schemas that have evolved over years without regular review.

Performance Best Practices for Indexing

• I index columns used in WHERE, JOIN, and ORDER BY clauses based on actual query patterns, not guesswork.
• I put the most selective and most frequently filtered columns first in composite indexes, keeping range-filtered columns last.
• I avoid indexing columns with very low cardinality (like a boolean flag) on their own, since the optimizer often won’t find such an index useful compared to a full scan.
• I periodically review sys.schema_unused_indexes and sys.schema_redundant_indexes to keep the schema lean.
• I run ANALYZE TABLE after major data changes so the optimizer’s statistics stay accurate.

Frequently Asked Questions

Q: How many indexes should a table have? A: There’s no fixed number — I add exactly as many as my actual query patterns require, and no more, since every index adds write overhead.

Q: Does adding an index always make queries faster? A: Not necessarily. For very small tables, a full scan can actually be faster than using an index due to the overhead of extra lookups. The optimizer usually gets this right, but I verify with EXPLAIN.

Q: What’s the difference between a primary key index and a unique index? A: In InnoDB, the primary key is the clustered index that determines physical row storage order; a unique index is a secondary index that only enforces uniqueness without controlling physical storage.

Q: Can I add an index without locking the table? A: In most cases yes — MySQL 8.0 supports online DDL for creating most types of indexes, allowing concurrent reads and writes during index creation, though I still schedule it carefully on very large, heavily-written tables.

Interview Questions I’ve Encountered

1. Explain the difference between a clustered index and a secondary index in InnoDB.
2. What is the “leftmost prefix rule” for composite indexes?
3. What is a covering index, and how does it improve query performance?
4. Why might adding too many indexes hurt overall database performance?
5. How would you determine the ideal prefix length for a prefix index?
6. Walk through how you’d diagnose why MySQL isn’t using an index you created.

Summary and Key Takeaways

Indexing is, in my experience, the single highest-leverage skill for MySQL performance work. Understanding B+Trees, the difference between clustered and secondary indexes, and the leftmost prefix rule has let me turn multi-second queries into sub-millisecond ones more times than I can count.

Key takeaways:

• InnoDB’s primary key is the clustered index; secondary indexes point back to it.
• Composite indexes only help queries that use a left-prefix of the indexed columns.
• Covering indexes let MySQL avoid touching the table data entirely.
• Always verify index usage with EXPLAIN rather than assuming.
• Periodically audit for unused and redundant indexes to keep write performance healthy.

References

• MySQL 8.0 Reference Manual, Optimization and Indexes: https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html
• MySQL InnoDB Index Types: https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html
• MySQL sys Schema: https://dev.mysql.com/doc/refman/8.0/en/sys-schema.html