Between and有条件的使用索引,做了如下的测试
1.表的结构如下Create Table
CREATE TABLE `city` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(30) DEFAULT NULL,
`subcity` int(11) DEFAULT NULL,
`tdate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`cont` varchar(2000) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_idx` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=81 DEFAULT CHARSET=utf8
2. 测试结果如下
测试场景1
表数据量是10条:
SELECT * FROM city WHERE id
BETWEEN 1 AND 5; 5/10使用索引
SELECT * FROM city WHERE id BETWEEN 1 AND 6; 不使用索引
测试场景2
表数据量是条14:
SELECT * FROM city WHERE id
BETWEEN 1 AND 5 ; 5/14使用索引
SELECT * FROM city WHERE id BETWEEN 1 AND 6; 不使用索引
测试场景3
表数