我有一个要应用索引的查询。查询是
select * from table where (id1='12' AND id2='17') OR (id1='17' AND id2='12');
现在我尝试使用(id1,id2)应用索引,但解释查询显示"类型"为"ALL"当我尝试(id2,id1)或id1和id2时,反应仍然相同。那么如何对id1和id2进行索引来处理上述查询。
mysql> explain select * from bothTable where (id1=12 and id2=17) OR (id1=17 and id2=12);
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | bothTable | ALL | NULL | NULL | NULL | NULL | 67 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> create index index_id1 ON bothTable (id1,id2);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from bothTable where (id1=12 and id2=17) OR (id1=17 and id2=12);
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | bothTable | ALL | index_id1 | NULL | NULL | NULL | 67 | Using where |
+----+-------------+------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
与联合,它给了我以下的输出:
+----+--------------+------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------+------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | bothTable | ALL | index_id1 | NULL | NULL | NULL | 67 | Using where |
| 2 | UNION | bothTable | ALL | index_id1 | NULL | NULL | NULL | 67 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)
create index id1_id2 on table(id1, id2);
此外,如果您的id1和id2列是整数列,请不要在整数周围加",而是尝试以下操作:
select * from table where (id1=12 AND id2=17) OR (id1=17 AND id2=12);
示例:
mysql> explain select * from ff where (id1 = '12' and id2 = '17') or (id1 = '17' and id2 = '12');
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | ff | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> create index id1_id2 on ff(id1,id2);
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from ff where (id1 = 12 and id2 = 17) or (id1 = 17 and id2 = 12);
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | ff | index | id1_id2 | id1_id2 | 10 | NULL | 7 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
编辑既然这似乎仍然不起作用,试试这个:
explain
select * from ff where (id1 = 12 and id2 = 17)
union
select * from ff where (id1 = 17 and id2 = 12);