你可以使用“col1 OR col2”在mysql中使用索引吗？ [英] Can you use index in mysql using "col1 OR col2"?

问题描述

我有一个mysql查询，它获取私人消息列表，其中用户是发件人或接收者。

I have a mysql query that gets a list of private messages where a user is either the sender, or receiver.

    SELECT 
    users_user1.user_name AS pm_username_1, 
    users_user1.user_avatar AS pm_username_1_avatar,
    users_user2.user_name AS pm_username_2,
    users_user2.user_avatar AS pm_username_2_avatar, 
    pms.*
FROM pm pms
LEFT JOIN users users_user1 
    ON users_user1.user_id = pms.pm_sender
LEFT JOIN users users_user2
    ON users_user2.user_id = pms.pm_receiver
WHERE pm_thread = pm_id 
    AND (pm_receiver = '1' OR pm_sender = '1')
    AND pm_delete != '1'
ORDER by pm_thread_last DESC LIMIT 0, 15

问题是....据我所知...它不能使用任何索引。

The problem is.... as far as I can tell... it cannot use any index.

我可以解决这个问题吗？

Any way I can get around that?

EDIT

+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                    | rows  | Extra                       |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+
|  1 | SIMPLE      | pms         | ALL    | pm_receiver   | NULL    | NULL    | NULL                   | 25354 | Using where; Using filesort |
|  1 | SIMPLE      | users_user1 | eq_ref | PRIMARY       | PRIMARY | 4       | movies.pms.pm_sender   |     1 |                             |
|  1 | SIMPLE      | users_user2 | eq_ref | PRIMARY       | PRIMARY | 4       | movies.pms.pm_receiver |     1 |                             |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+

将架构改为：

(SELECT 
    users_user1.user_name AS pm_username_1, 
    users_user1.user_avatar AS pm_username_1_avatar,
    users_user2.user_name AS pm_username_2,
    users_user2.user_avatar AS pm_username_2_avatar, 
    pms.*
FROM pm pms
LEFT JOIN users users_user1 
    ON users_user1.user_id = pms.pm_sender
LEFT JOIN users users_user2
    ON users_user2.user_id = pms.pm_receiver
WHERE pm_thread = pm_id 
    AND (pm_receiver = '1')
    AND pm_delete != '1')
UNION
(SELECT 
    users_user1.user_name AS pm_username_1, 
    users_user1.user_avatar AS pm_username_1_avatar,
    users_user2.user_name AS pm_username_2,
    users_user2.user_avatar AS pm_username_2_avatar, 
    pms.*
FROM pm pms
LEFT JOIN users users_user1 
    ON users_user1.user_id = pms.pm_sender
LEFT JOIN users users_user2
    ON users_user2.user_id = pms.pm_receiver
WHERE pm_thread = pm_id 
    AND (pm_sender = '1')
    AND pm_delete != '1')
ORDER by pm_thread_last DESC LIMIT 0, 15

EXPLAIN

+----+--------------+-------------+--------+---------------+-------------+---------+------------------------+------+----------------+
| id | select_type  | table       | type   | possible_keys | key         | key_len | ref                    | rows | Extra          |
+----+--------------+-------------+--------+---------------+-------------+---------+------------------------+------+----------------+
|  1 | PRIMARY      | pms         | ref    | pm_receiver   | pm_receiver | 4       | const                  |  336 | Using where    |
|  1 | PRIMARY      | users_user1 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_sender   |    1 |                |
|  1 | PRIMARY      | users_user2 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_receiver |    1 |                |
|  2 | UNION        | pms         | ref    | pm_sender     | pm_sender   | 4       | const                  |  283 | Using where    |
|  2 | UNION        | users_user1 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_sender   |    1 |                |
|  2 | UNION        | users_user2 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_receiver |    1 |                |
| NULL | UNION RESULT | <union1,2>  | ALL    | NULL          | NULL        | NULL    | NULL                   | NULL | Using filesort |
+----+--------------+-------------+--------+---------------+-------------+---------+------------------------+------+----------------+

推荐答案

是的，MySQL可以在OR表达式中使用索引。你怎么知道它没有使用你的索引，你是否使用EXPLAIN来查看MySQL如何运行你的查询？那张表中有多少行？如果行数太小，那么MySQL不会使用索引，因为它可以更快地进行全表扫描。我认为阈值是100 - 如果一个表的行少于100行，那么总是进行表扫描而不是使用索引。

Yeah, MySQL can use an index in an OR expression. How do you know its not using your index, did you use EXPLAIN to see how MySQL is running your query? How many rows do you have in that table? If the row count is too small then MySQL wont use an index as its faster to do a full table scan. I think the threshold is 100 - if a table has less than 100 rows than it will always do a table scan versus using an index.

这篇关于你可以使用“col1 OR col2”在mysql中使用索引吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！