When Does Adding an Index to a Status Column Actually Help? MySQL Deep Dive

Simple Execution Flow with an Index

Assume an order_info table with a B‑tree index idx_order_status on the order_status column. A query such as SELECT * FROM order_info WHERE order_status = 'FINISHED' triggers the following steps:

The engine traverses the B‑tree from the root, using binary search at each level, until it reaches the leaf nodes that contain the value FINISHED.

All matching leaf entries are scanned to collect the primary‑key IDs of the qualifying rows.

For each primary‑key ID, MySQL performs a lookup in the clustered primary‑key index to retrieve the full row – a process known as a table lookup (or “回表”).

Extreme Cases: Index vs Full Table Scan

If every row in the table has order_status = 'FINISHED', the optimizer recognizes that using the index would require many leaf scans and repeated table lookups, which is slower than a full table scan. In such cases MySQL chooses a full scan.

Even when only a few rows have a different status (e.g., a handful of 已下单 rows among millions of FINISHED rows), the index still may not be beneficial because the overhead of index navigation and row lookups outweighs the cost of scanning the whole table.

Order Status Selectivity

The effectiveness of an index depends on the column’s selectivity (cardinality). High selectivity—many distinct values with low repetition—makes the index useful. Low selectivity—few distinct values repeated many times—reduces the benefit and can even degrade performance.

You can inspect the index’s cardinality with

SHOW INDEX FROM order_info WHERE Key_name = 'idx_order_status';

. A low cardinality value indicates limited usefulness.

Impact of Result Set Size

When a query returns a large proportion of rows (e.g., all FINISHED orders), MySQL may still prefer a full table scan because the cost of fetching rows via the index (including the extra I/O for table lookups) can be higher.

Optimization techniques include using a covering index that contains all needed columns (e.g., a composite index on order_status and order_id) to avoid the table lookup entirely.

Combining Other Columns – Composite Indexes

In real scenarios queries often filter by additional columns such as client_no or create_time. Adding a composite index like idx_client_no_order_status or idx_create_time_order_status can dramatically improve performance because the index can satisfy both predicates.

Partitioned Tables

For tables with tens of millions of rows, partitioning by a high‑selectivity column (e.g., order_status or another suitable field) can limit scans to relevant partitions, reducing I/O.

Using EXPLAIN to Verify Index Usage

Before finalizing a query, run

EXPLAIN SELECT * FROM order_info WHERE order_status = 'FINISHED';

to inspect the execution plan. Look for type values like index or ref. If the plan shows type = ALL, MySQL is performing a full table scan, indicating the index is not being used.

By analyzing the plan, you can decide whether to keep, modify, or drop the index, or to adopt alternative strategies such as covering indexes, composite indexes, or partitioning.