mysql> explain SELECT /* 127.0.0.1 */ count(*) FROM `smw_ids` as t1 IGNORE INDEX(idx_smw_sortkey) inner join `smw_atts2` as t2 on t1.smw_id=t2.s_id inner join `smw_ids` as t3 on t2.p_id=t3.smw_id WHERE t1.smw_namespace='1180' and t3.smw_title='Product_Category' and t2.value_xsd='Operating systems' and t1.smw_sortkey<'U';
+----+-------------+-------+--------+---------------------+-----------+---------+-------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+-----------+---------+-------------------+--------+-------------+
| 1 | SIMPLE | t2 | ref | s_id,p_id,value_xsd | value_xsd | 257 | const | 750288 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 8 | dev.t2.s_id,const | 1 | Using where |
| 1 | SIMPLE | t3 | ref | PRIMARY,smw_title | PRIMARY | 4 | dev.t2.p_id | 1 | Using where |
+----+-------------+-------+--------+---------------------+-----------+---------+-------------------+--------+-------------+
3 rows in set (0.01 sec)
mysql> explain SELECT /* 127.0.0.1 */ count(*) FROM `smw_ids` as t1 force INDEX(primary) inner join `smw_atts2` as t2 on t1.smw_id=t2.s_id inner join `smw_ids` as t3 on t2.p_id=t3.smw_id WHERE t1.smw_namespace='1180' and t3.smw_title='Product_Category' and t2.value_xsd='Operating systems' and t1.smw_sortkey<'U';
+----+-------------+-------+--------+---------------------+-----------+---------+-------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------------+-----------+---------+-------------------+--------+-------------+
| 1 | SIMPLE | t2 | ref | s_id,p_id,value_xsd | value_xsd | 257 | const | 750288 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 8 | dev.t2.s_id,const | 1 | Using where |
| 1 | SIMPLE | t3 | ref | PRIMARY,smw_title | PRIMARY | 4 | dev.t2.p_id | 1 | Using where |
+----+-------------+-------+--------+---------------------+-----------+---------+-------------------+--------+-------------+
3 rows in set (0.01 sec)
我先IGNORE INDEX,发现强制忽略后,使用了PRIMARY,然后试着强制使用PRIMARY看看,俩个执行计划完全一样
执行时间:mysql> SELECT /* 127.0.0.1 */ count(*) FROM `smw_ids` as t1 force INDEX(primary) inner join `smw_atts2` as t2 on t1.smw_id=t2.s_id inner join `smw_ids` as t3 on t2.p_id=t3.smw_id WHERE t1.smw_namespace='1180' and t3.smw_title='Product_Category' and t2.value_xsd='Operating systems' and t1.smw_sortkey<'U';
+----------+
| count(*) |
+----------+
| 18154 |
+----------+
1 row in set (5.28 sec)
mysql> SELECT /* 127.0.0.1 */ count(*) FROM `smw_ids` as t1 IGNORE INDEX(idx_smw_sortkey) inner join `smw_atts2` as t2 on t1.smw_id=t2.s_id inner join `smw_ids` as t3 on t2.p_id=t3.smw_id WHERE t1.smw_namespace='1180' and t3.smw_title='Product_Category' and t2.value_xsd='Operating systems' and t1.smw_sortkey<'U';
+----------+
| count(*) |
+----------+
| 18154 |
+----------+
1 row in set (3.70 sec)
忽略索引的3.7,强制索引的5.28,这怎么解释??
OS: centos 5.5 X64online
Query OK, 0 rows affected (44.65 sec)
mysql> SELECT /* 127.0.0.1 */ count(*) FROM `smw_ids` as t1 force INDEX(primary) inner join `smw_atts2` as t2 on t1.smw_id=t2.s_id inner join `smw_ids` as t3 on t2.p_id=t3.smw_id WHERE t1.smw_namespace='1180' and t3.smw_title='Product_Category' and t2.value_xsd='Operating systems' and t1.smw_sortkey<'U';
+----------+
| count(*) |
+----------+
| 18154 |
+----------+
1 row in set (4.31 sec)
mysql> SELECT /* 127.0.0.1 */ count(*) FROM `smw_ids` as t1 IGNORE INDEX(idx_smw_sortkey) inner join `smw_atts2` as t2 on t1.smw_id=t2.s_id inner join `smw_ids` as t3 on t2.p_id=t3.smw_id WHERE t1.smw_namespace='1180' and t3.smw_title='Product_Category' and t2.value_xsd='Operating systems' and t1.smw_sortkey<'U';
+----------+
| count(*) |
+----------+
| 18154 |
+----------+
1 row in set (3.85 sec)
mysql> 还是相差有0.5S