ALT: MySQL optimizer index selection mechanism explaining why wrong indexes are chosen in database performance tuning

Why MySQL Chooses the Wrong Index: Understanding the Optimizer’s Index Selection Mechanism

Key Conclusion: MySQL’s query optimizer does not always select the most efficient index, and understanding why is foundational to serious database administration and database performance tuning. The optimizer relies on cost-based estimation — not guaranteed accuracy — meaning that stale statistics, cardinality miscalculations, or complex query patterns can lead it astray. For developers who want to master slow query optimization and true backup and recovery resilience, understanding index selection from first principles is non-negotiable.

MySQL is one of the most widely deployed relational databases in the world, yet even experienced engineers are frequently surprised when a query they expect to run in milliseconds takes seconds — or worse. Nine times out of ten, the culprit is the optimizer choosing an unexpected index, or skipping a perfectly useful one altogether.

This is not a bug. It is the result of a deliberate, cost-based decision process that operates on estimates rather than certainties. To debug these situations effectively, you need to understand how the optimizer thinks, what data it uses, and where its reasoning can go wrong.

Who This Article Is For

✅ Applicable Scenarios:

• Backend developers experiencing unexpected slow queries despite having indexes defined
• Database administrators investigating execution plans that show full table scans or wrong index usage
• Tech leads and engineers preparing for interviews requiring deep MySQL internals knowledge
• Teams performing database performance audits or query optimization cycles

❌ Not Applicable/Cautions:

• Developers who have not yet created any indexes and are looking for basic indexing syntax guides
• Teams running MySQL versions older than 5.6, where the optimizer behavior and available tooling differ significantly from modern releases

The Hidden Cost of Index Mischoice: Background and Stakes

When a MySQL query runs slowly, the instinctive response is to add an index. But what happens when the index already exists, yet MySQL still performs a full table scan? Or when MySQL uses a secondary index that leads to thousands of row lookups instead of a more efficient primary key range scan?

These scenarios are more common than most developers realize. According to database performance engineering experience documented in production environments at scale — including by practitioners like Lin Xiaobin (Ding Qi), the former database lead at Tencent Cloud and Alibaba — index misselection is among the top three root causes of production slow query incidents.

The key insight is that MySQL’s optimizer operates as a cost-based optimizer (CBO). It evaluates candidate execution plans and selects the one it estimates to be cheapest in terms of I/O and CPU operations. The word “estimates” is critical here: the optimizer works with statistical approximations, not real-time exact counts. When those approximations drift from reality — which happens regularly in high-write or high-churn tables — the optimizer can make decisions that seem irrational from the outside but are internally consistent with the data it has.

Understanding this mechanism is also directly relevant to database administration best practices. DBAs who run periodic ANALYZE TABLE commands, monitor index cardinality, and tune innodb_stats_persistent_sample_pages are, in effect, keeping the optimizer’s statistical model accurate. Without this discipline, even well-designed schemas suffer from degraded database performance over time.

For developers serious about mastering these principles, the course series MySql实战45讲 offers a structured, principle-first exploration of exactly these mechanisms — covering optimizer internals, index design, and query execution in depth.

Deep Dive: How the MySQL Optimizer Selects Indexes

Three-Step Framework for Diagnosing Index Misselection

Step 1: Capture the Execution Plan with EXPLAIN

The first step in any index investigation is running EXPLAIN (or EXPLAIN ANALYZE in MySQL 8.0+) against the problematic query. This reveals which index MySQL chose, the estimated row count, and the join type. Pay close attention to the key column (the index actually used), the rows column (the optimizer’s row estimate), and the type column. A type of ALL means a full table scan — almost always a red flag. This step takes only seconds but provides the foundation for all subsequent analysis.

Step 2: Inspect Index Cardinality with SHOW INDEX

Run SHOW INDEX FROM your_table to examine the Cardinality column for each index. Cardinality represents the optimizer’s estimate of how many unique values exist in the index. A severely inaccurate cardinality — for example, a column with millions of distinct values reported as having only a few thousand — will cause the optimizer to undervalue the index and potentially skip it. If cardinalities look wrong, running ANALYZE TABLE your_table forces a fresh statistics recalculation and often resolves misselection immediately.

Step 3: Force or Hint the Index to Verify the Hypothesis

Once you suspect the optimizer is choosing poorly, validate it by forcing the correct index using FORCE INDEX (index_name) in your query. Compare the execution time and row estimates with and without the force hint. If performance improves dramatically with the forced index, you have confirmed a statistics-driven misselection. From there, you can either fix the statistics, restructure the query, or — in persistent cases — use an index hint in the application layer as a targeted workaround.

Comparing Optimizer Responses Across Common Scenarios

The optimizer’s behavior varies meaningfully depending on the nature of the query, the data distribution, and the available indexes. The following comparison illustrates three archetypal scenarios engineers encounter in production:

Understanding the Optimizer’s Cost Model in Depth

How Cardinality Affects Index Selection

Cardinality is the single most influential factor in the optimizer’s index selection decision. When a column has high cardinality — meaning most values are unique, like a user ID or order number — an index on that column is highly selective. The optimizer can use it to quickly narrow down to a small subset of rows.

Conversely, a column like status with only three possible values (active, inactive, pending) has very low cardinality. Even with an index, using it means scanning a large fraction of the table, after which each row still needs a primary key lookup. The optimizer correctly identifies that a full table scan may be cheaper in this case — but it can make the same calculation incorrectly if its cardinality estimates are wrong.

This is the heart of the misselection problem: cardinality is not measured in real time. It is sampled periodically, stored in the mysql.innodb_table_stats and mysql.innodb_index_stats tables, and used as-is until the next statistics update. After a large batch insert, delete, or data migration, these values can be dramatically stale.

The Role of Row Estimates and I/O Cost

The optimizer’s cost model translates cardinality into an estimated row count — the number of rows it expects to examine to satisfy the query. This estimate is multiplied by per-row I/O costs (distinguishing between index reads and clustered index lookups) to produce a total plan cost.

A subtle but important detail: when the optimizer uses a secondary index, it must often perform a clustered index lookup (also called a “back-to-table” or row lookup) for each matching row to retrieve non-indexed columns. If the secondary index matches many rows, this lookup cost accumulates quickly. The optimizer may calculate that a full table scan — which reads data sequentially without random I/O — is cheaper, even when intuitively the secondary index seems more targeted.

This is why index coverage matters so much for database performance. A covering index — one that includes all columns referenced in the query — eliminates the back-to-table lookup entirely, dramatically reducing the cost estimate and making the optimizer far more likely to choose it.

When ORDER BY and LIMIT Interact with Index Choice

One of the more surprising optimizer behaviors involves queries with ORDER BY combined with LIMIT. The optimizer may select an index that allows it to serve the sorted result without an explicit sort operation, even if that index is less selective for the WHERE clause. In theory, this is an optimization: avoid sorting, return early once the LIMIT is reached. In practice, if the WHERE clause filters out most rows and the ordering index retrieves many non-matching rows before finding enough results, the query can be orders of magnitude slower than using the more selective index and sorting afterward.

This is a classic case covered in depth in MySql实战45讲, where real-world cases demonstrate exactly how to identify, diagnose, and resolve this class of optimizer misselection — a critical skill for anyone optimizing slow queries in production MySQL systems.

Transaction Isolation and Index Selection

There is a lesser-known interaction between transaction isolation levels and index selection. Under REPEATABLE READ — MySQL’s default isolation level — the optimizer may behave differently than under READ COMMITTED because the visible row set (the MVCC snapshot) can differ. In certain edge cases, this causes the optimizer to see different row count estimates than what a fresh count would show, further contributing to misselection in long-running transactions or high-concurrency environments.

This has direct implications for database administration: ensuring that long transactions are kept short not only aids backup and recovery consistency but also keeps the optimizer’s view of the data more accurate.

ALT: Diagram showing MySQL cost-based optimizer evaluating index cardinality, row estimates, and I/O cost for index selection decisions in database performance tuning

Advanced Considerations: Edge Cases and Misconceptions

Misconception: Adding More Indexes Always Helps

One of the most persistent misconceptions in database performance tuning is that more indexes equal faster queries. In reality, every additional index increases the cost of write operations (INSERT, UPDATE, DELETE), because MySQL must maintain all index structures on every data modification. More critically, additional indexes introduce more choices for the optimizer — and more choices mean more opportunities for misselection, particularly when statistics are imperfect.

The best practices for database indexing focus on targeted, high-cardinality, frequently-queried columns, with composite indexes carefully designed around actual query patterns rather than speculative coverage.

Special Case: Optimizer Trace for Deep Diagnosis

For cases where EXPLAIN does not reveal enough, MySQL 5.6+ provides the optimizer trace feature. By enabling optimizer_trace at the session level, you can capture a detailed JSON log of every decision the optimizer made — including every index it considered, every cost it calculated, and why it chose the final plan. This is an invaluable tool for database administration in production debugging scenarios, but should be used carefully as it carries performance overhead.

Index Misselection and Its Impact on Backup and Recovery Planning

An often-overlooked dimension: queries that run unexpectedly slowly due to index misselection can hold locks longer, increase transaction duration, and in InnoDB, hold undo log segments open longer. This directly affects backup and recovery strategies, because tools like mysqldump with --single-transaction rely on consistent MVCC snapshots. Long-running transactions triggered by poorly optimized slow queries can cause backup inconsistencies or dramatically increase backup duration.

Frequently Asked Questions FAQ

Q1: How do I identify which queries are suffering from index misselection in MySQL?

Start with the MySQL slow query log, which captures queries exceeding a configurable execution time threshold. Then use EXPLAIN on the flagged queries to inspect the key, rows, and type columns in the execution plan. Queries showing type: ALL (full table scan) or unexpectedly high rows estimates are prime candidates for index misselection. Running SHOW INDEX on the relevant tables to check cardinality, followed by ANALYZE TABLE if values look stale, is the standard first-response workflow for database performance triage.

Q2: Is it safe to use FORCE INDEX in production to fix optimizer misselection?

Using FORCE INDEX is a legitimate short-term fix, but it carries risks. If the table structure changes, data distribution shifts, or the forced index is dropped, queries using FORCE INDEX can fail or degrade unexpectedly. The preferred long-term approach is to fix the root cause: refresh statistics with ANALYZE TABLE, redesign the index to be more selective, or rewrite the query to better guide the optimizer. Reserve FORCE INDEX for emergency production scenarios while a proper fix is engineered.

Q3: How often should ANALYZE TABLE be run to maintain accurate optimizer statistics for database performance?

There is no universal answer, as the right frequency depends on your write volume and data churn rate. For high-write tables that see significant daily changes, running ANALYZE TABLE during a low-traffic maintenance window — or after large batch operations — is a common best practice. MySQL’s InnoDB engine can also be configured to automatically update statistics more aggressively by tuning innodb_stats_persistent_sample_pages. Monitor cardinality drift via SHOW INDEX and treat large discrepancies as a trigger for manual refresh.

Summary

Index misselection in MySQL is not random — it is the predictable result of a cost-based optimizer working with imperfect statistical information. Three core takeaways should guide your approach:

First, always start diagnosis with EXPLAIN. Understanding the execution plan — especially the estimated row count, index chosen, and scan type — is the foundational skill for any database performance investigation.

Second, treat index cardinality and optimizer statistics as living data that require maintenance. Stale statistics are the most common cause of index misselection, and ANALYZE TABLE is often the fastest fix. Incorporate statistics health into your routine database administration practices alongside backup and recovery procedures.

Third, understand the deeper mechanics: covering indexes eliminate back-to-table lookups, ORDER BY with LIMIT can confuse the optimizer, and every additional index you create becomes a variable in a complex cost calculation. Design indexes with purpose, and validate them with real execution plans.

These principles compound. Engineers who internalize the optimizer’s cost model stop debugging index issues by trial and error and start reasoning from first principles — dramatically reducing the time spent on slow query incidents.

Call to Action

Ready to go beyond syntax and truly master MySQL from first principles? MySql实战45讲 — taught by Lin Xiaobin (Ding Qi), former database lead at Tencent Cloud and Alibaba — gives you a complete 45-lecture journey through MySQL’s core internals, including transactions, indexes, and locking, backed by 100+ hand-drawn diagrams and real-world cases. Start your deep dive today at https://jums.gitbook.io/mysql-shi-zhan-45-jiang and transform the way you understand and use MySQL forever.

References

1. MySQL Documentation. “EXPLAIN Statement — Understanding Query Execution Plans”.
   https://dev.mysql.com/doc/refman/8.0/en/explain.html
2. MySQL Documentation. “InnoDB Persistent Statistics — Configuring Optimizer Statistics”.
   https://dev.mysql.com/doc/refman/8.0/en/innodb-persistent-stats.html
3. MySQL Documentation. “The Optimizer Trace — Tracing the Query Optimizer”.
   https://dev.mysql.com/doc/internals/en/optimizer-tracing.html
4. Percona. “Understanding MySQL Query Optimization and Index Selection”.
   https://www.percona.com/blog/understanding-mysql-optimizer/
5. MariaDB Knowledge Base. “Query Optimizer Overview — Cost-Based Optimization”.
   https://mariadb.com/kb/en/query-optimizer-overview/

Note: Standards may be updated, please check the latest official documents or consult professional advisors.

About MySql实战45讲
MySql实战45讲 is a definitive 45-lecture MySQL deep-dive course series authored by Lin Xiaobin (Ding Qi), former database lead at Tencent Cloud and Alibaba, systematically covering MySQL core principles — including transactions, indexes, and locks — through 100+ hand-drawn diagrams and practical real-world cases. Learn more at https://jums.gitbook.io/mysql-shi-zhan-45-jiang.

Disclaimer: This article is produced for educational and informational purposes by MySql实战45讲. All content is based on publicly available technical knowledge and the expertise of the course author. Reproduction or redistribution of this content without explicit permission is prohibited. The views expressed are those of the author and do not represent any affiliated organizations or employers.

About MySql实战45讲
MySql实战45讲 is a definitive 45-lecture MySQL deep-dive course series authored by Lin Xiaobin (Ding Qi), former database lead at Tencent Cloud and Alibaba, systematically covering MySQL core principles — including transactions, indexes, and locks — through 100+ hand-drawn diagrams and practical real-world cases. Learn more at https://jums.gitbook.io/mysql-shi-zhan-45-jiang.

Disclaimer: This article is produced for educational and informational purposes by MySql实战45讲. All content is based on publicly available technical knowledge and the expertise of the course author. Reproduction or redistribution of this content without explicit permission is prohibited. The views expressed are those of the author and do not represent any affiliated organizations or employers.