-- 创建索引
-- 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条