MySQL использует индексы для нескольких OR, но не использует индекс для IN и намного медленнее

В запросе:

SELECT * FROM teams WHERE 'Joe Bloggs' IN (Team1,Team2,Team3)

вы сравниваете (ищете) группу столбцов со строковым литералом. Оптимизатор обычно использует индекс цели поиска, в данном случае Joe Bloggs, чтобы найти значение в предложении IN. Но он не может поместить индекс в строковый литерал. Итак, здесь все наоборот, и поэтому индекс не поможет.

С другой стороны, в вашем первом запросе:

SELECT * FROM teams WHERE Team1='Joe Bloggs' OR Team2='Joe Bloggs' OR Team3='Joe Bloggs'

MySQL будет захватывать строковые литералы, а затем сравнивать их с различными столбцами, используя индекс B-дерева. Это ведет себя так, как вы ожидаете и видите.

У вас есть «перевернутый IN»; оптимизатор будет использовать индекс только для column in (value1, value2, value3).

Но если у вас есть отдельные индексы для каждого из трех столбцов, есть другой способ, который должен обеспечить гораздо лучшую производительность, чем любая из ваших попыток:

SELECT * FROM teams WHERE Team1='Joe Bloggs'
UNION
SELECT * FROM teams WHERE Team2='Joe Bloggs'
UNION
SELECT * FROM teams WHERE Team3='Joe Bloggs'

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

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

План A: используйте FULLTEXT (team1, team2, team3) и MATCH(team1, team2, team3) AGAINST ('+Joe +Briggs' IN BOOLEAN MODE). Есть много предостережений при использовании этого подхода, но, если он применим в вашем случае, он будет очень быстрым.

План B: Несмотря на то, что «невозможно изменить формат таблицы», вы можете сыграть в какую-нибудь игру с VIEW, чтобы избежать распространения массива (команд) по столбцам.

Я не знаю, почему производительность была бы другой - индексы, похоже, не использовались бы ни в том, ни в другом случае.

Вы можете написать запрос следующим образом:

SELECT t.*
FROM teams t
WHERE Team1 = 'Joe Bloggs'
UNION ALL
SELECT t.*
FROM teams t
WHERE Team2 = 'Joe Bloggs' AND Team1 <> 'Joe Bloggs' 
UNION ALL
SELECT t.*
FROM teams t
WHERE Team3 =  'Joe Bloggs'
  AND Team2 <> 'Joe Bloggs'
  AND Team1 <> 'Joe Bloggs';

Это может использовать индексы на (Team1), (Team2, Team1) и (Team3, Team2, Team1).