Common MySQL Index Failure Scenarios and Optimization Techniques

1. Introduction

In daily development, database‑related problems frequently arise, such as indexes being used but queries still timing out, excessive indexes slowing writes, indexes not fully covering queries, unexpected fileSort operations, and bulk data processing inefficiencies.

2. Analyzing SQL

2.1 Using the Execution Plan

Use EXPLAIN to obtain the SQL execution plan.

Typical fields and common Extra values are shown in the following images:

Difference between using where and using index condition:

When using index condition is present, the storage engine filters rows using the index (ICP), reducing the number of rows that need to be fetched from the table.

2.2 Tracing the Optimizer

The optimizer trace shows SQL transformation, table dependencies, full‑table scan and index cost calculations, and filesort algorithm selection.

3. Case Analysis

Sample table definition:

<code style="padding:16px;color:#abb2bf;font-family:Operator Mono,Consolas,Monaco,Menlo,monospace;font-size:12px">CREATE TABLE `reserve` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT 'database auto‑increment id',
  `reserve_no` VARCHAR(32) NOT NULL COMMENT 'unique reservation number',
  `shop_id` BIGINT UNSIGNED NOT NULL COMMENT 'shop id',
  `uid` BIGINT UNSIGNED NOT NULL COMMENT 'buyer id',
  `technician_id` BIGINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'technician id',
  `arrange_at` DATETIME NOT NULL COMMENT 'reservation start time',
  `is_delete` TINYINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'logical delete flag',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
  `reserve_status` INT NOT NULL DEFAULT '5' COMMENT 'reservation status',
  `guest_name` VARCHAR(30) NOT NULL DEFAULT '' COMMENT 'guest name',
  `guest_mobile` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'guest mobile',
  `real_pay` BIGINT UNSIGNED NOT NULL DEFAULT '0' COMMENT 'total price',
  PRIMARY KEY (`id`),
  KEY `idx_reserve_no` (`shop_id`,`reserve_no`) COMMENT 'reservation number',
  KEY `idx_uid_kdt` (`shop_id`,`uid`) COMMENT 'user id',
  KEY `idx_shop_guest_name` (`shop_id`,`guest_name`) COMMENT 'guest name',
  KEY `idx_status_arrange` (`shop_id`,`reserve_status`,`arrange_at`) COMMENT 'status & time',
  KEY `idx_shop_create_status` (`shop_id`,`created_at`,`reserve_status`) COMMENT 'shop creation time',
  KEY `idx_shop_del` (`shop_id`,`is_delete`) COMMENT 'logical delete',
  KEY `idx_shop_dept_arrange` (`shop_id`,`arrange_at`,`created_at`),
  KEY `idx_guest_name` (`guest_name`) COMMENT 'guest name'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='reservation record table';</code>

Note: using index condition can reduce back‑table reads, but the index itself may still not be optimal for certain queries.

3.1 Index Failure Scenarios

Left‑most matching truncation : Composite indexes require the leftmost columns in the query; e.g., SELECT * FROM reserve WHERE reserve_no='' will not use the index unless shop_id is also specified.

Implicit conversion : Comparing a string column with a numeric literal (or date with a string) forces MySQL to convert types, causing index loss.

IN + ORDER BY : Using IN together with ORDER BY can block index usage; reordering the index fields or selecting only id first can help.

Range query blocking composite index : A range condition (e.g., created_at > '2020‑10‑22') stops the optimizer from using later index columns.

Leading wildcard : LIKE '%name%' cannot use the index; only trailing wildcards can be used.

OR condition : Queries like uid=1 OR guest_name='' prevent index usage; separate indexes with index merge may improve performance.

Function or arithmetic in WHERE : Using functions (e.g., DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(created_at)) or arithmetic ( real_pay+1=10) disables index usage.

NOT IN / != : These operators also lead to index loss.

Low selectivity : Columns with very low selectivity (e.g., is_delete) should not be part of an index.

Index fragmentation : Heavy DML can fragment indexes, causing unexpected performance degradation.

3.2 Index Optimization Scenarios

Remove low‑selectivity columns from composite indexes (e.g., drop is_delete from idx_shop_del).

Do not combine unique columns with others; keep a single‑column unique index for reserve_no.

Place high‑selectivity columns first in composite indexes (e.g., move uid before reserve_status).

Use covering indexes for aggregation queries, e.g., add real_pay to the index for SELECT SUM(real_pay) ….

3.3 SQL Writing Optimization

3.3.1 Deep pagination

Deep pagination ( LIMIT 1000,10) forces the engine to read many rows before applying the limit, leading to massive back‑table reads. Replace it with id‑based pagination:

<code style="padding:16px;color:#abb2bf;font-family:Operator Mono,Consolas,Monaco,Menlo,monospace;font-size:12px"># Avoid deep pagination
SELECT * FROM reserve WHERE id > last_id LIMIT 100;

# Delayed join to reduce back‑table reads
SELECT * FROM shop t1, (SELECT id FROM reserve LIMIT 1000,100) t2 WHERE t1.id = t2.id;</code>

3.3.2 Mixed ASC/DESC ordering

Mixing ascending and descending order on different columns (e.g., ORDER BY arrange_at DESC, created_at ASC) can cause the index to be ignored. Use a single sort direction or adjust the index accordingly.

3.3.3 Other tips

When possible, select only indexed columns to enable covering index scans.

For large data sets, split processing by time ranges and run in parallel.

Summary

Follow the left‑most rule for composite indexes.

Avoid implicit type conversion.

Be careful with IN + ORDER BY patterns.

Place range‑filtered columns at the end of composite indexes.

Leading wildcards prevent index usage.

OR conditions may require separate indexes and index merge.

Functions and arithmetic in WHERE clauses disable indexes.

NOT IN and != also lead to index loss.

Do not index columns with very low selectivity.