Exuding Speed: Fixing MySQL Index Mistakes That Cripple Performance

If your MySQL queries are slowing down, the culprit is often hiding in plain sight: the indexes you created with good intentions. A missing index can turn a fast lookup into a full table scan. But an extra index on a write-heavy table can cause more pain than it prevents. We've seen teams add indexes reactively, only to find their insert performance cratering. This guide walks through the most common index mistakes—and how to fix them without guesswork.

1. Where Index Problems Surface in Real Work

Index mistakes don't announce themselves with a single error. They show up gradually: a dashboard query that used to take 200ms now takes 12 seconds. A nightly batch job that finishes in an hour starts timing out. The common thread is that the database is doing more work than necessary—reading rows it could skip, sorting data it could retrieve in order, or locking pages it shouldn't touch.

In one typical scenario, a team built an e-commerce platform with a products table containing millions of rows. They added an index on the category_id column because the most frequent query filtered by category. But the query also sorted by price and filtered by a date range. The single-column index helped a little, but the database still had to sort and filter the remaining rows in memory. Adding a composite index on (category_id, price, created_at) cut query time from 8 seconds to 40 milliseconds.

Another common situation is the over-indexed table. A logging system that inserts 10,000 rows per second had five indexes on a 12-column table. Each insert had to update all five indexes, causing lock contention and slow writes. Removing three unused indexes reduced insert latency by 60% without affecting read queries—because those indexes were never used.

Index mistakes also appear in join-heavy queries. A reporting system joined four tables with millions of rows each. The join columns were indexed, but the WHERE clause used functions like DATE(created_at) = '2024-01-01', which made the index on created_at useless. Changing the condition to created_at >= '2024-01-01' AND created_at < '2024-01-02' allowed the index to be used, dropping query time from 45 seconds to under 1 second.

Where to start looking

If you suspect index problems, begin with the slow query log. Identify queries that appear frequently and have high execution time. Run EXPLAIN on those queries and look for 'Using where; Using filesort' or 'Using temporary'—those are signs that indexes are missing or poorly designed. Also check index usage with the performance_schema.table_io_waits_summary_by_index_usage table to see which indexes are never read.

2. Foundations That Readers Often Confuse

Understanding how MySQL indexes work at a basic level helps avoid common mistakes. An index is a data structure (usually a B-tree) that stores a copy of selected columns, sorted, so the database can find rows quickly without scanning the entire table. But there are nuances that trip up even experienced developers.

Clustered vs. secondary indexes

In InnoDB, the primary key index is clustered—the leaf nodes contain the actual row data. Secondary indexes store the primary key value as a pointer to the row. This means a secondary index lookup requires two steps: find the primary key in the secondary index, then look up the row in the clustered index. That's why choosing a good primary key matters: a long primary key (like a UUID) makes all secondary indexes larger and slower.

Composite index column order

The order of columns in a composite index is critical. MySQL can use the index for equality conditions on leading columns, then range conditions, then sorting. But if you put a low-selectivity column first, the index may not help as much. For example, an index on (status, created_at) works well if you filter by status='active' and then sort by created_at. But if you filter by created_at alone, the index is useless because created_at is not the leading column. The rule: put the most selective column first (the one with the most distinct values) for equality conditions.

Indexes and sorting

MySQL can use an index to avoid a filesort if the ORDER BY columns match the index order exactly, and if the sort direction matches. But if you mix ASC and DESC in the ORDER BY, the index may not be used unless it's a descending index (MySQL 8.0+). Also, if you sort by a column that is not part of any index, MySQL will sort in memory or on disk, which can be slow for large result sets.

Cardinality and selectivity

Cardinality is the number of distinct values in an indexed column. An index on a column with low cardinality (like a boolean flag) is not very selective—it reduces the search space only by half. But if you combine it with a high-cardinality column in a composite index, the selectivity improves. Many teams mistakenly index low-cardinality columns alone, expecting them to speed up queries, but the optimizer often ignores such indexes because scanning the whole table is cheaper.

3. Patterns That Usually Work

After years of observing what works in production, a few index patterns stand out as reliable. These aren't silver bullets, but they handle the majority of common query patterns well.

Covering indexes for frequent queries

A covering index contains all the columns needed by a query, so MySQL can satisfy the query entirely from the index without touching the table. This is the fastest way to read data. For example, if a query selects only id and name from a table where status='active', an index on (status, name, id) covers the query. The EXPLAIN output will show 'Using index' (not 'Using where; Using index').

Composite indexes on filter-and-sort patterns

When a query filters by one column and sorts by another, a composite index on (filter_column, sort_column) usually works well. The index handles the filter with a range scan and returns rows in sorted order, avoiding a filesort. This pattern is common in paginated listings: WHERE category_id = ? ORDER BY created_at DESC.

Prefix indexes for long strings

For very long VARCHAR columns, indexing the entire column can be wasteful. MySQL allows prefix indexes: INDEX (last_name(10)). This indexes only the first N characters. The trade-off is that prefix indexes cannot be used for sorting or covering queries, but they can speed up equality lookups on long strings. Use them when the column is longer than 200 characters and the first few characters are sufficiently selective.

Partial indexes (MySQL 8.0.13+)

MySQL 8.0 introduced functional indexes and partial indexes via generated columns. You can create an index on an expression, like INDEX ((lower(email))). This is useful for case-insensitive lookups without forcing the application to normalize data. But remember: the index is only used if the query uses the exact same expression.

4. Anti-Patterns and Why Teams Revert

Even with good intentions, teams often implement index patterns that backfire. Here are the anti-patterns we see most frequently, along with reasons why they get reverted.

Over-indexing every column

Adding an index on every column that appears in a WHERE clause sounds safe, but it creates overhead. Each index must be updated on INSERT, UPDATE, and DELETE. For write-heavy tables, this can cause performance degradation. We've seen a table with 12 indexes on 15 columns where a single insert took 500ms because of index maintenance. The fix: drop unused indexes. Use the performance_schema to identify indexes with zero reads.

Indexing low-cardinality columns alone

An index on a boolean column like is_deleted (values 0 or 1) is almost never used by the optimizer. Scanning half the table via an index is often slower than a full table scan due to random I/O. The exception is when combined with other columns in a composite index, where the low-cardinality column is the leading column and the query filters by both columns.

Function-wrapped columns in WHERE

Writing WHERE YEAR(created_at) = 2024 prevents MySQL from using an index on created_at. The function hides the column value from the index lookup. The fix is to rewrite as a range condition: created_at >= '2024-01-01' AND created_at < '2025-01-01'. This allows the index to be used for a range scan.

Ignoring index merge

MySQL can use multiple indexes per table via index merge (intersection or union). But relying on index merge is often a sign that a composite index would be better. Index merge can be slower than a single composite index because it requires reading multiple B-trees and combining results. If you see 'Using union' or 'Using intersect' in EXPLAIN, consider creating a composite index that covers the conditions.

Too many indexes on the same leading column

If you have indexes on (a, b), (a, c), and (a, d), the first index can often serve queries that filter on a alone, or a and b. But having multiple indexes with the same leading column is redundant. The optimizer can use the first index for queries that need a, and for queries that need a and b. The other indexes may be unnecessary. Consolidate them into a single composite index that covers the most common query patterns.

5. Maintenance, Drift, and Long-Term Costs

Indexes are not set-and-forget. Over time, as data grows and query patterns change, indexes that once helped can become liabilities. Regular maintenance is essential to keep performance stable.

Index fragmentation

As rows are inserted, updated, and deleted, B-tree indexes become fragmented. Fragmentation means the index pages are not optimally packed, leading to more disk I/O. You can check fragmentation with SHOW TABLE STATUS and look at the Data_free column. For InnoDB, rebuilding the table with OPTIMIZE TABLE can defragment indexes, but it locks the table. For large tables, consider using pt-online-schema-change from Percona Toolkit to rebuild without downtime.

Drift in query patterns

Applications evolve. A query that was once rare becomes frequent. An index that was added for a specific feature may become unused after a code change. We recommend setting up a periodic review of index usage—quarterly for most systems. Use the sys schema's schema_unused_indexes view to find indexes that have never been read. Drop them to reduce write overhead.

Index bloat from large primary keys

If your primary key is a UUID (36 bytes), every secondary index stores a copy of that UUID. For a table with 10 secondary indexes and 10 million rows, that's 3.6 GB of extra storage just for the primary key pointers. Switching to a sequential integer or a UUID shortener (like using BINARY(16) for UUIDs) can reduce index size significantly.

Cost of index maintenance on high-write tables

Each index adds overhead to write operations. For a table that receives 10,000 writes per second, each additional index adds roughly 10,000 index updates per second. This can saturate disk I/O and cause replication lag. If your table is write-heavy, limit indexes to those absolutely necessary for read queries. Consider using a separate read replica for reporting queries to offload index overhead from the primary.

6. When Not to Use This Approach

Not every query benefits from an index, and sometimes the best index strategy is to not index at all. Here are situations where the standard index advice may not apply.

Very small tables

If a table has fewer than a few thousand rows, a full table scan is often faster than an index lookup because of the overhead of traversing the B-tree. In such cases, indexes are unnecessary and add write overhead. Measure before adding indexes to small lookup tables.

Data warehouse or analytical queries scanning large portions of a table

If a query reads more than 20-30% of a table, a full table scan can be more efficient than an index scan due to sequential I/O. In these cases, consider using summary tables or a columnar storage engine (like MySQL HeatWave or a separate OLAP system) instead of indexing every column.

Write-heavy tables with low read requirements

For tables that are primarily written to (like logs or event streams) and rarely read, indexes add cost without benefit. Keep only the indexes needed for the few read queries. If reads are infrequent, consider using a separate read-only copy of the data for analysis.

When the query uses non-deterministic functions

Functions like RAND() or NOW() in WHERE clauses cannot use indexes because the value changes each time. In such cases, no index can help. Restructure the query to use deterministic conditions, or accept that a full scan is inevitable.

When the index is larger than the table

If an index on a wide column (like a long VARCHAR) is larger than the table itself, the index may not provide a performance benefit. The database has to read the index, then look up rows, which can be slower than a full table scan. In such cases, consider a prefix index or a hash index (for MEMORY tables) if appropriate.

7. Open Questions and FAQ

How do I know if an index is being used?

Use EXPLAIN on your query. If the 'possible_keys' column shows the index and the 'key' column shows it being used, the index is used. Also check the 'Extra' column: 'Using index' means a covering index; 'Using where; Using index' means the index is used for filtering but not covering. You can also query the performance_schema.table_io_waits_summary_by_index_usage to see cumulative read/write counts per index.

What is the difference between a unique index and a regular index?

A unique index enforces uniqueness of the indexed column(s) and allows only one NULL value per column (in MySQL). A regular index allows duplicates and multiple NULLs. Unique indexes can be used for lookups just like regular indexes, but they also provide a constraint. The performance difference is minimal; choose based on whether you need the constraint.

Can I have too many indexes on a table?

Yes. Each index adds overhead for write operations and consumes disk space. A rule of thumb: for a table with high write volume, keep the number of indexes under 5-7. For read-heavy tables, you can have more, but monitor index usage and drop unused ones. Use the sys.schema_unused_indexes view to find candidates for removal.

Should I index foreign key columns?

Yes, MySQL does not automatically index foreign key columns. If you join on a foreign key, you need an index on that column to avoid full table scans. InnoDB requires an index on the referencing column for foreign key constraints, but it may not create one automatically if a suitable index exists. Always check and add an index if missing.

How do I choose between a composite index and multiple single-column indexes?

If your queries filter by multiple columns together, a composite index is usually better. Single-column indexes are useful when queries filter by different columns independently, and MySQL can use index merge. But index merge is not always efficient. Test both approaches with realistic data volumes. For most cases, composite indexes on the most common query patterns win.

Next steps: Run EXPLAIN on your top 10 slow queries. Identify any 'Using filesort' or 'Using temporary' and add composite indexes accordingly. Then monitor performance and index usage for a week. Drop any index that hasn't been read. Repeat quarterly to keep your database fast.