Understanding MySQL Index Optimization and Execution Flow

Efficient database design aims to minimize resource access, and index selection directly influences the number of tree searches and row scans required by a query.

1. SQL Execution Flow – For the statement select * from T where k between 3 and 5; MySQL first locates the first matching key in the k index, retrieves the corresponding primary‑key row, then continues scanning the k index until the range ends, resulting in three index reads and two table look‑ups (back‑table operations).

2. Common Index Optimizations

2.1 Covering Index – If the query can be satisfied entirely by the index (e.g., select ID from T where k between 3 and 5; ), the engine avoids back‑table look‑ups.

2.2 Left‑most Prefix Rule – Composite indexes are used only from the leftmost column onward; a query that omits the first column cannot use the index.

2.3 Index Condition Push‑down (ICP) – Starting with MySQL 5.6, predicates on indexed columns are evaluated during index traversal, reducing the number of rows that need to be fetched from the table.

2.4 Implicit Type Conversion – Mismatched data types between columns and query literals prevent index usage; fixing this requires altering the column type or casting the literal.

3. Why the Optimizer May Choose the Wrong Index

3.1 Optimizer Cost Model – The optimizer estimates execution cost based on scanned rows, index cardinality, and other factors such as temporary tables and sorting.

3.2 Cardinality Estimation – MySQL samples index pages to estimate distinct values; inaccurate statistics lead to poor index choices. Statistics can be persisted or kept in memory via the innodb_stats_persistent setting.

When an index is mis‑chosen, you can:

Use FORCE INDEX to force a specific index.

Rewrite the query to make the desired index applicable.

Create a more suitable index or drop the misleading one.

Run ANALYZE TABLE to refresh statistics.