??????????????????

????1???????????????????????????￡?????????????????????????????????????????????У?????????????????????????????????????????????????????н???????????????????

????2?????????С?????????????????磺???????????? status??????ν???????

???????????stats?????0~4???????????

mysql> SELECT SQL_NO_CACHE count(id) FROM test.test WHERE stats=4;
+———–+
| count(id) |
+———–+
| 740591 |
+———–+
1 row in set (0.40 sec)
mysql> ALTER TABLE `test`.`test` add INDEX `stats` (`stats`);
Query OK?? 3702592 rows affected (24.08 sec)
Records: 3702592 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_NO_CACHE count(id) FROM test.test WHERE stats=4;
+———–+
| count(id) |
+———–+
| 740591 |
+———–+
1 row in set (1.58 sec)

????3??????????????????????????????￡????????????м?????????????????????????????????????￡??????????Ч????????????????????????????5????飬??????????????????????????????????????????????????????

?????????????

mysql> SELECT COUNT(*) FROM test.newtest; //????????10000??
+———-+
| COUNT(*) |
+———-+
| 10001 |
+———-+

????????????????????????0??

mysql> SELECT SQL_NO_CACHE count(time1) FROM test.`newtest` WHERE time1 >’20130517100591′;
+————–+
| count(time1) |
+————–+
| 9992 |
+————–+

???????????

mysql> ALTER TABLE `test`.`newtest` ADD INDEX `time1` (`time1`);
Query OK?? 10001 rows affected (0.04 sec)
Records: 10001 Duplicates: 0 Warnings: 0

????????????????????0.02sec

mysql> SELECT SQL_NO_CACHE count(time1) FROM test.`newtest` WHERE time1 >’20130517100591′;
+————–+
| count(time1) |
+————–+
| 9992 |
+————–+
1 row in set (0.02 sec)

????4????????????????????????????????????????????A??B??????ε?????????????????????A?????????????????????????????????????????????á????????????????????????????????????????????????????????????????Ч???

????5????????????????????????????????κ?????????????????Σ?????????????????????á???????????????it1c1c2(c1??c2)????????select * from t1 where c1=1 and c2=2???????????????????select * from t1 where c1=1?????????????????????????select * from t1 where c2=2???????????????????????????????????У???????????c2?н??в???????????c1???????????????????????????????????????????η????檔

???????磺

ALTER TABLE `test`.`friends` ADD INDEX `fuid_fname` (`fuid`?? `fname`)?????????????
mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fname=’test5′;
+—-+————-+———+——+—————+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——+———+——+——–+————-+
| 1 | SIMPLE | friends | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+—-+————-+———+——+—————+——+———+——+——–+————-+

???????????????????fuid???????????????????????

mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fuid=’364′;
+—-+————-+———+——+—————+————+———+——-+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+————+———+——-+——-+————-+
| 1 | SIMPLE | friends | ref | fuid_fname | fuid_fname | 9 | const | 33712 | Using where |
+—-+————-+———+——+—————+————+———+——-+——-+————-+