Как участвует индекс при использовании JOIN к другим таблицам?

Question

[bad_shape_guy]

Как будет и будет ли участвовать индекс, когда в запросе происходит подключение к дополнительным таблицам? Например, имеется таблица EMPLOYEE с полями FULL_NAME, DEPARTMENT, AGE.
Запрос к таблице один и тот же:

SELECT full_name, department, age
  FROM employee
 WHERE department = '100'
 ORDER BY full_name, age

Чтобы ускорить запрос добавляю индекс с последовательностью полей: department, full_name, age.
И тут возникает вопрос, а что если запрос немного поменять и подключить дополнительную таблицу:

SELECT e.full_name, e.department, e.age
  FROM employee e
  LEFT JOIN department d
    ON d.number = e.department
 WHERE e.department = '100' AND
       d.code = '50'
 ORDER BY e.full_name, e.age

Как после этого будет использоваться индекс, и будет ли он, точнее интересует влияет ли на использование индекса JOIN-ы в запросе, если влияют то как в таких случаях строить индекс?

Comments

[Akina]

Ваш запрос выполняет INNER JOIN. Так что LEFT не имеет смысла.

[shurshur]

Неправильно. Индекс надо по тому условию, которое в where, а не по комбинации столбцов в select.

Как работает индекс? В отдельном месте хранится каким-то образом оптимизированная (хеши, b-дерево итд) структура значений поля или полей, которая содержит ссылку на строки таблиц, подходящие под это значение. Поэтому при запросе мы будем перебирать не всю таблицу, а только те строки, которые упомянуты в индексе.

Например, если есть индекс по department, то мы из условия department=100 можем найти строки с таким значением. А если у нас индекс по трём столбцам в комбинации, то он не может использоваться для условия department=100.

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

Что именно будет делаться при выполнении запроса и какие индексы будут использоваться показывает EXPLAIN.

[Emil Revencu]

Поля что участвуют в связях для JOIN лучше чтобы имели индексы если они не PRIMARY