Why Your MySQL Index Fails: 8 Common Pitfalls and How to Fix Them

This guide demonstrates why MySQL indexes may become ineffective and shows how to diagnose each case using a sample tony_order table populated with over one million rows.

Preparation

A table tony_order is created with several indexed columns, including composite indexes such as (user_id, total_amount). The large dataset ensures realistic execution plans.

CREATE TABLE `tony_order` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `product_id` int NOT NULL COMMENT '商品ID',
  `user_id` int NOT NULL COMMENT '用户ID',
  `status` tinyint NOT NULL COMMENT '状态',
  `discount_amount` int NOT NULL COMMENT '总金额',
  `total_amount` int NOT NULL COMMENT '打折金额',
  `payable_amount` int NOT NULL COMMENT '实际支付金额',
  `receiver_name` varchar(255) DEFAULT NULL COMMENT '收件人名称',
  `receiver_phone` varchar(255) DEFAULT NULL COMMENT '收件人手机号',
  `receiver_address` varchar(255) DEFAULT NULL COMMENT '收件人地址',
  `note` varchar(255) DEFAULT NULL COMMENT '备注',
  `payment_time` datetime DEFAULT NULL COMMENT '支付时间',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id` DESC) USING BTREE,
  INDEX `idx_product_id`(`product_id` ASC) USING BTREE,
  INDEX `idx_user_id_total_amount`(`user_id` ASC, `total_amount` ASC) USING BTREE,
  INDEX `idx_create_time`(`create_time` ASC) USING BTREE,
  INDEX `idx_update_time`(`update_time` ASC) USING BTREE,
  INDEX `idx_status`(`status` ASC) USING BTREE,
  INDEX `idx_receiver_phone`(`receiver_phone` ASC) USING BTREE,
  INDEX `idx_receiver_name`(`receiver_name` ASC) USING BTREE,
  INDEX `idx_receiver_address`(`receiver_address` ASC) USING BTREE
) ENGINE=InnoDB CHARACTER SET=utf8mb4 ROW_FORMAT=Dynamic;

1. Not Following the Left‑most Prefix Rule

Query:

SELECT * FROM tony_order WHERE total_amount = 100;

The execution plan shows a full table scan because total_amount is not the leftmost column of the composite index (user_id, total_amount). Adding the leftmost column fixes the issue:

SELECT * FROM tony_order WHERE user_id = 4323 AND total_amount = 101;

Since MySQL 8.0, the optimizer can apply the Skip Scan Range Access Method to improve low‑cardinality columns, but the leftmost‑prefix rule remains the primary guideline.

2. LIKE with a Leading Wildcard

Queries with a leading ‘%’ cannot use an index:

SELECT * FROM tony_order WHERE receiver_address LIKE '%北京市朝阳区望京SOHO';
SELECT * FROM tony_order WHERE receiver_address LIKE '%北京市朝阳区望京SOHO%';

Moving the wildcard to the right allows the index to be used:

SELECT * FROM tony_order WHERE receiver_address LIKE '北京市朝阳区望京SOHO%';

3. Using OR

When both columns have indexes, the optimizer can merge them:

SELECT * FROM tony_order WHERE receiver_name = 'Tony学长' OR user_id = 41323;

However, if one side lacks an index, the whole query falls back to a full scan:

SELECT * FROM tony_order WHERE receiver_phone = '13436669764' OR user_id = 4323;

The optimizer prefers the most efficient plan, so sometimes an “index‑failure” is actually the optimal choice.

4. Functions on Indexed Columns

Applying a function prevents index usage:

SELECT * FROM tony_order WHERE ABS(user_id) = 4323;

Similarly, using LEFT(receiver_address,3) = '北京市' disables the index.

SELECT * FROM tony_order WHERE LEFT(receiver_address, 3) = '北京市';

5. Calculations on Indexed Columns

Expressions such as user_id + 1 = 4324 also break index usage:

SELECT * FROM tony_order WHERE user_id + 1 = 4324;

6. Implicit Type Conversion

Comparing a numeric column to an unquoted number forces a conversion and disables the index:

SELECT * FROM tony_order WHERE receiver_phone = 13454566332;

Quoting the value restores index usage:

SELECT * FROM tony_order WHERE receiver_phone = '13454566332';

7. Large Result Sets (NOT IN / IN)

Using NOT IN that returns most rows forces a full scan:

SELECT * FROM tony_order WHERE product_id NOT IN (12345,12346);

Conversely, status NOT IN (0,1) may still use the index if the optimizer estimates a small result set.

SELECT * FROM tony_order WHERE status NOT IN (0,1);

When the query would return a large proportion of rows (e.g., status IN (0,1) on a 1‑million‑row table), the optimizer also prefers a full scan.

SELECT * FROM tony_order WHERE status IN (0,1);

8. Mismatched ORDER BY Directions

If the ORDER BY clause orders columns in different directions than the index, the index cannot be used:

SELECT * FROM tony_order ORDER BY user_id DESC, total_amount ASC LIMIT 10;

When both columns share the same sort direction, the index can be applied:

SELECT * FROM tony_order ORDER BY user_id ASC, total_amount ASC LIMIT 10;
SELECT * FROM tony_order ORDER BY user_id DESC, total_amount DESC LIMIT 10;

Understanding these patterns helps you design indexes that the MySQL optimizer can actually use, improving query performance.