Why Do My Database Indexes Fail? 7 Common Pitfalls and How to Build Effective Indexes

Why Indexes Fail

Indexes can dramatically speed up queries, but they are ignored when certain patterns appear in SQL statements. The article groups the main causes into seven mnemonic categories: 模 (fuzzy), 型 (type), 数 (function), 空 (NULL), 运 (operation), 最 (leftmost), 快 (full‑scan).

模 (Fuzzy) : Using LIKE with a leading wildcard prevents index usage. Example:

SELECT * FROM user WHERE name LIKE '%极客技术';

型 (Type) : Mismatched column and literal types break the index. Example: SELECT * FROM user WHERE height = 10; If height is defined as VARCHAR , the index is not used.

数 (Function) : Applying a function to an indexed column disables the index unless a functional index exists. Example:

SELECT * FROM user WHERE DATE(create_time) = '2020-09-03';

空 (NULL) : Indexes do not store NULL values. If a column is not declared NOT NULL, the optimizer may avoid the index. Examples:

SELECT * FROM user WHERE address IS NULL;  -- does not use index
SELECT * FROM user WHERE address IS NOT NULL;  -- uses index

Setting a default empty string for optional fields can mitigate this issue.

运 (Operation) : Arithmetic or other operations on indexed columns break index usage. Example: SELECT * FROM user WHERE age - 1 = 20; 最 (Leftmost) : In a composite index, the query must reference the leftmost column(s). If the leftmost column is not used, the index is ignored.

快 (Full‑scan Faster) : When the optimizer estimates that a full table scan is cheaper than using the index, it will choose the scan.

How to Build Effective Indexes

When asked how to design indexes, consider the following principles:

Index columns that are frequently joined with other tables (e.g., foreign keys).

Index columns that appear often in WHERE clauses, especially on large tables.

Prefer small‑size columns for indexes; avoid indexing large text or BLOB fields.

Do not over‑index tables that experience heavy write operations, as each index adds overhead.

Create indexes on columns used for sorting ( ORDER BY), grouping ( GROUP BY), or deduplication ( DISTINCT).

Fields Not Suitable for Indexes

Columns that are rarely used in queries should not be indexed.

Columns with a high proportion of identical values (low cardinality) provide little benefit.

If write performance (INSERT/UPDATE/DELETE) is far more critical than read performance, avoid adding indexes that would slow down modifications.

Properly designed indexes can significantly improve query speed, while inappropriate indexes may degrade overall performance.