目的:验证MyISAM表是否or条件也会用索引
版本:5.5.53试验1:根据http://blog.csdn.net/z69183787/article/details/46731665该文章开头的测试,完全复制建表语句和explain,得出的结果是一堆nullid: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY,uid
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
-------------试验2:自己建表CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) NOT NULL DEFAULT '0',
`type` int(11) NOT NULL DEFAULT '0',
`time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `age` (`age`),
KEY `type` (`type`),
KEY `time` (`time`),
KEY `all` (`age`,`type`,`time`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
分析语句:EXPLAIN SELECT * FROM user WHERE id=1 or age =8 and type = 3 or time = 99\G;结果:id: 1
select_type: SIMPLE
table: user
type: ALL
possible_keys: PRIMARY,age,type,time,all
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
请问我哪里弄错了吗?
版本:5.5.53试验1:根据http://blog.csdn.net/z69183787/article/details/46731665该文章开头的测试,完全复制建表语句和explain,得出的结果是一堆nullid: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY,uid
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
-------------试验2:自己建表CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`age` int(11) NOT NULL DEFAULT '0',
`type` int(11) NOT NULL DEFAULT '0',
`time` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `age` (`age`),
KEY `type` (`type`),
KEY `time` (`time`),
KEY `all` (`age`,`type`,`time`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
分析语句:EXPLAIN SELECT * FROM user WHERE id=1 or age =8 and type = 3 or time = 99\G;结果:id: 1
select_type: SIMPLE
table: user
type: ALL
possible_keys: PRIMARY,age,type,time,all
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
请问我哪里弄错了吗?
这是一种怎样的逻辑?and 在这里完全失效的。SELECT * FROM user WHERE id=1
UNION
SELECT * FROM user WHERE age =8
UNION
SELECT * FROM user WHERE time = 99
select_type: SIMPLE
table: user
type: ALL
possible_keys: age,type,time,all
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using where
1 row in set (0.10 sec)
谢谢,这回我有别的发现,请看:mysql> EXPLAIN SELECT * FROM user WHERE age =8 or type = 3 or time = 99\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: index_merge
possible_keys: age,type,time,all
key: age,type,time
key_len: 4,4,4
ref: NULL
rows: 3
Extra: Using union(age,type,time); Using where
1 row in set (0.07 sec)
这里key是有3个对吗,根据以前的浅显了解,通常每次查询只会用1个索引的
这回用了多个索引,意味着MyISAM表在or条件下也会使用多个索引对吧