CREATE TABLE `users` ( `userID` int(11) NOT NULL AUTO_INCREMENT, `userName` varchar(20) NOT NULL, `password` varchar(20) NOT NULL, PRIMARY KEY (`userID`) ); delimiter $ create procedure genUsers() begin declare i int default 0; while i < 100000 do set i = i + 1; insert into users(userID,userName,`password`) values(i,concat(‘username’,i),concat(‘password’,i)); end while; end $ delimiter ; call genUsers(); 复制表结构和数据
CREATE TABLE users2 SELECT * FROM users;
添加userName,password单列索引
alter table users add index userName(userName);
alter table users add index password(password);
select * from users where userName like 'username65%' and password like 'password65%'; 执行时间0.06 秒
alter table users2 add index userName_password(userName,password);
select * from users2 where userName like 'username65%' and password like 'password65%’;执行时间0.00秒
上面的结果每台机子测试的结果可能有所不同 http://www.phpsong.com/586.html
分类: web
标签: