mysql - 为什么where条件中or加索引不起作用？

Question

CREATE TABLE test (
id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
creator varchar(128) NOT NULL DEFAULT '0' COMMENT '创建人',
gmt_create timestamp NULL DEFAULT NULL COMMENT '创建时间',
modifier varchar(128) DEFAULT '0' COMMENT '修改人',
gmt_modified timestamp NULL DEFAULT NULL COMMENT '修改时间',
title varchar(64) DEFAULT NULL COMMENT '工单标题',
category varchar(32) DEFAULT NULL COMMENT '工单类别',
subject varchar(32) DEFAULT NULL COMMENT '工单类型',
demander varchar(30) DEFAULT NULL COMMENT '需求方',
is_atomic char(1) DEFAULT 'y' COMMENT '是否原子工单',
atomic_id int(11) DEFAULT NULL COMMENT '当前原子工单在列表中ID',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=182431 DEFAULT CHARSET=utf8 COMMENT='测试表';

SHOW INDEX FROM test

ALTER TABLE test ADD INDEX test_title (title)

EXPLAIN SELECT * FROM test.test WHERE id = 100 or title = 'gg'

EXPLAIN SELECT * FROM test.test WHERE title = 'ggg'

Answer 1

First, the amount of data is too small. When formulating the execution plan, I found that the entire table is faster. You can try filling in millions of data with different IDs and titles into the table.
Second, make sure mysql version is 5.0 or above, and the query optimizer is turned on index_merge_union=on, that is, optimizer_switch exists in the variable index_merge_union and is on

Additional point:

@mokeyjay’s statement is not very correct and outdated. You can take a look at the following documents.

http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

Answer 2

It’s a cliché, just quote a blog post http://blog.csdn.net/hguisu/article/details/7106159

Answer 3

Thanks for the invitation. Take a look at this document about the indexing problem of or in mysql. I hope it will be helpful to you

Answer 4

I agree with what @xiayongsheng said. I found a table online containing tens of millions of data and explained or queried it

# id是主键， user_id是普通索引
explain SELECT * from t WHERE id = 100000 or user_id = 'c7b6752c37b111e6a7d705b57e583e2e';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	index_merge	PRIMARY,t_userid	PRIMARY,t_userid	4,123		2	Using union(PRIMARY,t_userid); Using where