Master MySQL Index Optimization: 10 Practical Tips to Avoid Index Pitfalls

This article uses an

employees

table as a concrete example to illustrate MySQL index practices, showing how to retrieve execution plans with the

EXPLAIN

keyword and referencing the official MySQL documentation for detailed output.

Used Table

<code>CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (id),
  KEY idx_name_age_position USING BTREE (name, age, position)
) ENGINE=InnoDB AUTO_INCREMENT=4 CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees (name, age, position, hire_time) VALUES ('LiLei', 22, 'manager', NOW());
INSERT INTO employees (name, age, position, hire_time) VALUES ('WaKen', 23, 'dev', NOW());
INSERT INTO employees (name, age, position, hire_time) VALUES ('Lucy', 23, 'dev', NOW());</code>

Composite Index Data Structure

Index Best Practices

1. Full‑Value Match

When the query conditions match all columns of an index (or the leftmost columns of a composite index), the index can be used efficiently.

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei';</code>

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22;</code>

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22 AND position='manager';</code>

2. Leftmost‑Prefix Rule

For multi‑column indexes, queries must start with the leftmost indexed column and cannot skip columns.

<code>EXPLAIN SELECT * FROM employees WHERE age=22 AND position='manager';</code>

<code>EXPLAIN SELECT * FROM employees WHERE position='manager';</code>

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei';</code>

3. Avoid Functions or Conversions on Indexed Columns

Using the index directly works:

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei';</code>

Applying

LEFT()

or other functions disables the index, causing a full table scan.

<code>EXPLAIN SELECT * FROM employees WHERE LEFT(name,3)='LiLei';</code>

4. Range Conditions Prevent Use of Right‑Side Columns

Full‑value match uses all index columns:

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=22 AND position='manager';</code>

Introducing a range condition (

age>22

) makes the subsequent column (

position

) unusable by the index.

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age>22 AND position='manager';</code>

5. Prefer Covering Indexes (Avoid SELECT * )

If all selected columns are part of the index, MySQL can retrieve rows directly from the index without accessing the table, improving performance.

<code>EXPLAIN SELECT name, age FROM employees WHERE name='Lilei' AND age=23 AND position='manage';</code>

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei' AND age=23 AND position='manager';</code>

6. Inequality ( != or <> ) Disables Index Use

<code>EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';</code>

7. IS NULL / IS NOT NULL Cannot Use Index on Variable‑Length Types

Because MySQL stores a separate null‑flag for variable‑length columns, these predicates prevent index usage. Define default values for columns whenever possible.

<code>EXPLAIN SELECT * FROM employees WHERE name IS NULL;</code>

8. Leading Wildcard in LIKE Causes Full Scan

<code>EXPLAIN SELECT * FROM employees WHERE name LIKE '%Lei';</code>

<code>EXPLAIN SELECT * FROM employees WHERE name LIKE 'Lei%';</code>

To make

LIKE '%string%'

use an index, employ a covering index and ensure the indexed column is not excessively large (e.g., varchar(380) or more may invalidate the covering index).

9. Implicit Type Conversion Breaks Indexes

Omitting quotes around string literals forces MySQL to convert types, causing the index to be ignored.

Correct usage with quotes:

<code>EXPLAIN SELECT * FROM employees WHERE name='1000';</code>

Without quotes, MySQL converts the value to a number, disabling the index.

<code>EXPLAIN SELECT * FROM employees WHERE name=1000;</code>

10. Avoid OR When Possible

<code>EXPLAIN SELECT * FROM employees WHERE name='LiLei' OR name='HanMeimei';</code>

Quick Summary

LIKE 'KK%' behaves like an equality comparison; LIKE '%KK' and LIKE '%KK%' act as range queries. The following checklist helps determine whether an index will be used or ignored.

Summary 1

Summary 2