Does MySQL’s OR Operator Use Indexes? Experimental Findings and Optimization Tips

A job candidate named Lao Wang, confident about his MySQL knowledge, failed an interview when asked whether the OR operator in a query can use indexes, revealing a common misunderstanding about index usage.

The article then sets up an experiment: it creates a t_student table, adds an index on the first_name column, and inserts 100,000 rows (the insertion can be done with a stored procedure referenced in a previous article).

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `first_name` varchar(10) NOT NULL COMMENT '名字',
  `last_name` varchar(32) NOT NULL COMMENT '姓氏',
  `gender` varchar(10) NOT NULL COMMENT '性别',
  `grade` tinyint(1) NOT NULL COMMENT '年级',
  `enroll_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `full_stack`.`t_student`
ADD INDEX `idx_first_name` (`first_name` ASC);

When both columns referenced by the OR clause have indexes, MySQL can use those indexes, as shown by the query plan image. However, if only one side is indexed, MySQL performs a full table scan, which is illustrated by a second image.

From these results, the article concludes that the OR operator will leverage indexes only when every operand column is indexed; otherwise, it degrades to a full scan.

To handle cases where only one side is indexed, the article recommends rewriting the query using UNION to combine two separate indexed queries, thereby preserving index usage.

Additional recommended readings on Redis data persistence and data types are provided via links at the end of the article.