MySQL索引的使用

-- 创建索引

-- CREATE INDEX index_message ON tb_log(message(255));

-- 创建唯一索引

-- CREATE UNIQUE INDEX index_datetime ON tb_log(datetime(255));

-- 删除索引

-- DROP INDEX index_message ON tb_log; 

-- select * from tb_log where datetime='2011-08-15 17:17:46,890'

-- delete from tb_log where id > 200

-- 查看索引

-- select index_name from all_indexes where table_name = 'tb_log';

-- 建立索引前：Time: 0.109ms

-- select * from ipsource where beginip='194.145.126.000'

-- CREATE INDEX index_beginip ON ipsource(beginip(30));

-- 建立索引后：Time: 0.000ms

-- select * from ipsource where beginip='194.145.126.000'

-- ALTER INDEX index_beginip ON ipsource(beginip(10));

-- 重建索引

-- ALTER TABLE ipsource DROP INDEX index_beginip;

-- ALTER TABLE ipsource ADD INDEX index_beginip (beginip(6)); 

注意：tb_log ：

CREATE TABLE `tb_log` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `datetime` varchar(255) DEFAULT NULL,

  `priority` varchar(255) DEFAULT NULL,

  `message` text,

  `classname` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `index_datetime` (`datetime`)

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

ipsource：

CREATE TABLE `ipsource` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `beginip` varchar(30) NOT NULL,

  `endip` varchar(30) NOT NULL,

  `area` varchar(60) DEFAULT NULL,

  `netservice` varchar(60) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `index_beginip` (`beginip`(6))

) ENGINE=InnoDB AUTO_INCREMENT=143101 DEFAULT CHARSET=utf8;

ipsource表中记录数大约7W条