向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

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

show index from  p2p_main;  //p2p_main 是表名称


mysql> show index from  p2p_main;      
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| p2p_main |          0 | PRIMARY     |            1 | id          | A         |     3052412 |     NULL | NULL   |      | BTREE      |         |
| p2p_main |          1 | oweneruid   |            1 | oweneruid   | A         |      610482 |     NULL | NULL   |      | BTREE      |         |
| p2p_main |          1 | oweneruid_2 |            1 | oweneruid   | A         |      610482 |     NULL | NULL   |      | BTREE      |         |
| p2p_main |          1 | oweneruid_2 |            2 | otheruid    | A         |     1017470 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

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 ;

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

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