100w测试数据,为什么加了索引查询反而变慢了?
yjl49 2012-02-07 06:48:21 建表:
create table tb_test(fval varchar(50));
----------------------------------------------
插入测试数据:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `P_teset`()
BEGIN
DECLARE v_val VARCHAR(20);
DECLARE v_str VARCHAR(20);
DECLARE v_i INT ;
DECLARE v_j INT;
SET v_str ='abcdefghijklmnopqrstuvwxyz';
SET v_i=0;
SET v_j=0;
WHILE v_i<600000 DO
SET v_val ='';
WHILE v_j< 13 DO
SET v_val= CONCAT(v_val,SUBSTRING(v_str,1,FLOOR(1+RAND()*26)));
SET v_j=v_j+1;
END WHILE;
SET v_j=0;
INSERT INTO tb_test(fval)
VALUES(v_val);
SET v_i =v_i+1;
END WHILE;
END$$
DELIMITER ;
----------------------------------------------
测试like:
SELECT *
FROM tb_test
WHERE fval LIKE '%ab';
70231条 用时0.531s
SELECT *
FROM tb_test
WHERE fval LIKE 'ab%'
961406条用时1.422s
---------------------------------------------
加索引:
ALTER TABLE tb_test ADD INDEX my_index(fval);
---------------------------------------------
再测试:
SELECT *
FROM tb_test
WHERE fval LIKE '%ab';
70231条 用时1.094s
SELECT *
FROM tb_test
WHERE fval LIKE 'ab%'
961406条用时1.485s
表为MyISAM格式。
为什么加了索引却慢了?