<![CDATA[向东博客专注WEB应用构架之美 --- 构架之美，在于尽态极妍

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

alter table table_name drop index index_name ;
alter table table_name drop primary key ;
drop index shili on tpsc ;
来源URL：http://www.isstudy.com/mysql/440.html
实际实践如下：

alter table `relation` drop index gots; alter table `relation` drop index gots;
alter table `relation` add index gots(`group`,`owneruid`, `type`,`status`);

create table index_analyse_xiangdong select * from relation limit 21000;

alter table `p2p_main` add index oweneruid_2(oweneruid ,otheruid );//建立索引,括号里面不能有逗号和单引号
alter table `p2p_main` drop index oweneruid_2; //删除索引：是建立在oweneruid 和otheruid 的联合索引

explain SELECT SQL_NO_CACHE * FROM `mytest` force index(rstatus,uid) WHERE rstatus =1 AND uid IN ( 3,2,7) \G;

测试用数据表结构如下：
--

-- 表的结构 `p2p_info`
--

CREATE TABLE IF NOT EXISTS `p2p_info` (
  `infoid` int(10) unsigned NOT NULL auto_increment,
  `group` enum('1','2') collate utf8_bin NOT NULL,
  `productid` char(32) collate utf8_bin NOT NULL,
  `connection` enum('11','22') collate utf8_bin NOT NULL,
  `invitetype` enum('111','222') collate utf8_bin NOT NULL,
  `content` text collate utf8_bin NOT NULL,
  `addition` text collate utf8_bin NOT NULL,
  PRIMARY KEY  (`infoid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- 表的结构 `p2p_main`
--

CREATE TABLE IF NOT EXISTS `p2p_main` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `oweneruid` int(11) NOT NULL,
  `otheruid` int(11) NOT NULL,
  `type` enum('1','2') NOT NULL default '1',
  `status` enum('11','22') NOT NULL default '11',
  `infoid` int(11) NOT NULL,
  `ctime` datetime NOT NULL,
  `uniq_key` varchar(64) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

1.PRIMARY  KEY（主键索引）
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  )
2.UNIQUE(唯一索引)
        mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (
`column` )   //这个不靠谱

alter table `index_analyse_xiangdong` ADD UNIQUE key id4(id);
alter table index_analyse_xiangdong drop index id4;

3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

可以用sql强制使用或不使用索引
IGNORE INDEX (invitetype)
USE INDEX (invitetype)
如
select count from p2p_info1 IGNORE INDEX (invitetype) where invitetype="111";

]]>