Mysql: внутреннее соединение по первичному ключу для 2 идентификаторов дает проверенный диапазон для каждой записи

Как правило, MySQL может использовать только один индекс для каждой ссылки на таблицу в запросе (существует ссылка index-merge, но это работает не так часто, как вы думаете).

В вашем условии соединения есть OR между двумя сравнениями с индексированными столбцами, и оптимизатор не может выбрать, какое из них лучше использовать, прежде чем данные в таблице будут проверены построчно.

Распространенным обходным решением является выполнение UNION между более простыми запросами вместо условия OR.

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 |
+----+--------------+------------+--------+---------------+---------+---------+--------------------+------+-----------------+

При этом используются правильные поиски по индексу в обоих подзапросах, но для последующего завершения UNION необходимо использовать временную таблицу. В конечном счете, это может быть промывка для производительности. Зависит от того, сколько строк данных необходимо проверить и сколько строк будет получено в результате.

Проблема хорошо известна и в других (думаю, во всех) СУБД, оптимизатор будет использовать только одно правило для каждого соединения.

Если условие соединения сложное или если он не может распознать известный шаблон для его решения, оптимизация применяться не будет, и будет выполнено полное сканирование таблицы.

В вашем случае условие OR в основном соединении кажется простым, но это не так, потому что вы просите проверить каждый идентификатор пользователя по двум разным столбцам (не постоянным значениям) за раз.

Чтобы решить эту проблему, вы должны разделить условие соединения на несколько подзапросов, чтобы оптимизатор мог использовать лучшее правило для каждого из них.

@Bill Karwin предложил общее решение, и оно хорошо помогает понять проблему.

(Немного) лучший способ решить эту проблему — переместить объединение на один уровень вверх и выполнить соединение с производной таблицей:

EXPLAIN 
SELECT *
FROM test_user AS u
INNER JOIN (
    select id, sender_id as msg_id
    from test_message 
    union all 
    select id, receiver_id 
    from test_message 
    ) AS m 
ON u.id = m.msg_id;

Он не будет использовать TEMPORARY таблиц и выполняет только одно полное сканирование таблицы на test_users вместо двух.

id  select_type table           partitions  type    possible_keys   key             key_len ref         rows    filtered    Extra
1   PRIMARY     u               NULL        ALL     PRIMARY         NULL            NULL    NULL        1       100.00      NULL
1   PRIMARY     <derived2>      NULL        ref     <auto_key0>     <auto_key0>     4       test.u.id   2       100.00      NULL
2   DERIVED     test_message    NULL        index   NULL            idx_sender      4       NULL        1       100.00      "Using index"
3   UNION       test_message    NULL        index   NULL            idx_receiver    4       NULL        1       100.00      "Using index"