Mysql: inner join on primary key for 2 IDs gives "Range checked for each record"

Question

I get a "Range checked for each record (index map: 0x1)" in EXPLAIN SELECT when doing an INNER JOIN on a PRIMARY key with 2 values (using either IN or OR constructs)

Here is the query:

SELECT *
FROM message AS m
INNER JOIN user AS u
ON u.id = m.sender_id OR u.id = m.receiver_id

When doing an explain, it gives me:

+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                                         |
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------------------------+
|  1 | SIMPLE      | u     | ALL  | PRIMARY       | null | null    | null | 75000 | Range checked for each record (index map: 0x1)|
+----+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------------------------+

It can't be...

If I try this I get the same result:

SELECT *
FROM message AS m
INNER JOIN user AS u
ON u.id IN(m.sender_id, m.receiver_id)

But if I do this, it works fine and I get only 1 row parsed:

SELECT *
FROM message AS m
INNER JOIN user AS u
ON u.id = m.sender_id

How is this possible? I'm joining on a primary key with same-type values. (the actual query is "a bit" more complicated but nothing fancy, 2 inner joins and lastly one left join)

It should be 2 rows, period.

Thanks for any input on this (did some research but did not find anything valuable except for "please add an index", which is obviously not applicable here)

EDIT: yes, I tried the USE INDEX statement, but still no luck

EDIT: Here is a very simple schema to reproduce this weird behavior of MySQL:

CREATE TABLE test_user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    PRIMARY KEY (id)
);

CREATE TABLE test_message (
    id INT NOT NULL AUTO_INCREMENT, 
    sender_id INT NOT NULL,
    receiver_id INT NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_sender (sender_id),
    INDEX idx_receiver (receiver_id)
);

EXPLAIN SELECT *
FROM test_message AS m
INNER JOIN test_user AS u
    ON u.id = m.sender_id OR u.id = m.receiver_id;

Answer 1

In general, MySQL can use only one index per table reference in a query (there's an index-merge algorithm, but this doesn't work as often as you might think).

Your join condition has an OR between two comparisons to indexed columns, and the optimizer can't choose which is the better one to use before the data in the table is examined row-by-row.

A common workaround is to do a UNION between simpler queries, instead of the OR condition.

mysql> EXPLAIN 
    SELECT * FROM test_message AS m 
    INNER JOIN test_user AS u ON u.id = m.sender_id 
  UNION
    SELECT * FROM test_message AS m 
    INNER JOIN test_user AS u ON u.id = m.receiver_id;

+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-----------------+
| id | select_type  | table      | type   | possible_keys | key     | key_len | ref                | rows | Extra           |
+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-----------------+
|  1 | PRIMARY      | m          | ALL    | idx_sender    | NULL    | NULL    | NULL               |    1 | NULL            |
|  1 | PRIMARY      | u          | eq_ref | PRIMARY       | PRIMARY | 4       | test.m.sender_id   |    1 | NULL            |
|  2 | UNION        | m          | ALL    | idx_receiver  | NULL    | NULL    | NULL               |    1 | NULL            |
|  2 | UNION        | u          | eq_ref | PRIMARY       | PRIMARY | 4       | test.m.receiver_id |    1 | NULL            |
| NULL | UNION RESULT | <union1,2> | ALL    | NULL          | NULL    | NULL    | NULL               | NULL | Using temporary |
+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-----------------+

This does use proper index lookups in both sub-queries, but it has to use a temporary table to finish the UNION afterwards. Ultimately, it might be a wash for performance. Depends on how many rows of data need to be examined, and how many rows are produced as the result.