Proszę o wyjaśnienie na jakie kolumny nakładać indeksy typu INDEX gdy mamy do czynienia z kilkoma tabelami i wszystkie one są wykorzystane do złączenia, warunku selekcji i grupowania. Manual MySQL'a wyjaśnia połowicznie gdyż mówi o indeksach jedno i wielokolumnowych ale tylko w przypadku jednej tabeli (chociaż wspomina, że indeksy wykorzystywane są także przy złączeniach JOIN. patrz niżej).
Załóżmy, że mamy takie 3 tabele (TABA, TABB, TABC):

            TABA                              TABB                              TABC
+---------+-----+---------+       +---------+-----+---------+       +---------+-----+---------+
|  POLE   | TYP |  KLUCZ  |       |  POLE   | TYP |  KLUCZ  |       |  POLE   | TYP |  KLUCZ  |
+---------+-----+---------+       +---------+-----+---------+       +---------+-----+---------+
|   ID  | INT | PRIMARY |       |   ID  | INT | PRIMARY |       |   ID  | INT | PRIMARY |
|  INTA   | INT |         |       |   IDA   | INT |  INDEX  |       |   IDB   | INT |  INDEX  |
|  INTB   | INT |         |       | X   | INT |         |       |   Y   | INT |         |
| VISIBLE | INT |         |       | VISIBLE | INT |         |       | VISIBLE | INT |         |
+---------+-----+---------+       +---------+-----+---------+       +---------+-----+---------+

Oraz zapytanie, które łączy te tabele:

SELECT taba.inta, taba.intb, tabb.x, tabc.y
  FROM taba
    JOIN tabb ON taba.id = tabb.ida
      JOIN tabc ON tabb.id = tabc.idb
        WHERE taba.visible = 1 AND tabb.visible = 1 AND tabc.visible = 1
          ORDER BY taba.inta, taba.intb, tabb.x, tabc.y

Oto fragment z manuala MySQL'a

MySQL uses indexes for these operations:

  • To find the rows matching a WHERE clause quickly.
  • To eliminate rows from consideration. …
  • If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. …
  • To retrieve rows from other tables when performing joins. …
  • To find the MIN() or MAX() value for a specific indexed column key_col. …
  • To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable index (for example, ORDER BY key_part1, key_part2). …
  • In some cases, a query can be optimized to retrieve values without consulting the data rows. …

To, że muszę dać indeksy na kolumny tabb.ida i tabc.idb jest dla mnie oczywiste bo w końcu łączę tabele z wykorzystaniem tych kolumn. Żeby łączenie było szybkie to daję indeksy na te kolumny by ich wyszukiwanie do złączenia było wydajne.
Ale dalej mam jeszcze klauzule WHERE i ORDER BY. Żeby selekcja i sortowanie były szybkie to jasne jest też, że muszę i na nie dać indeksy. I tu właśnie pojawia się coś czego manual nie tłumaczy. A dokładnie czy indeksy wielokolumnowe działają tylko gdy są w jednej klauzuli (np. wiele kolumn ale tylko po ON, albo tylko po WHERE lub tylko po ORDER BY), czy też działają przez całe zapytanie (od pierwszego wykorzystania kolumny do ostatniego) obojętnie z iloma tabelami następuje złączenie byle by kolejność pojawiania się nowych kolumn w trakcie przechodzenia przez zapytanie była zachowana?

Nałożyć indeksy na tabele TABA, TABB i TABC mogę na kilka sposobów, dla przykładu:
Rozumowanie nr 1:

CREATE TABLE taba (                   CREATE TABLE tabb (               CREATE TABLE tabc (
  ...............................       ...........................       .......................
  INDEX (id, visible, inta, intb)       INDEX (ida, id, visible, x)       INDEX (idb, visible, y)
)                                     )                                 )

W tym rozumowaniu kolumny z poszczególnych tabel daję do jednego indeksu w kolejności takiej w jakiej występują w zapytaniu, tj. Najpierw kolumny służące do warunku złączenia ON, następne te, które występują po WHERE a na końcu te które służą do sortowania.

Rozumowanie nr 2:

CREATE TABLE taba (                   CREATE TABLE tabb (               CREATE TABLE tabc (
  .......................               ......................            ...................
  INDEX (id),                           INDEX (ida, id),                  INDEX (idb),
  INDEX (visible),                      INDEX (visible),                  INDEX (visible),
  INDEX (inta, intb)                    INDEX (x)                         INDEX (y)
)                                     )                                 )

Tutaj rozdzieliłem te indeksy na grupy, które zawierają kolumny z danej klauzuli. Np. dla tabeli TABB istnieje indeks INDEX (ida, id) ponieważ zarówno ida jak i id należą do warunków złączenia (klauzuli ON), natomiast oddzielnie dałem indeksy na visible oraz x bo każda z tych kolumn należy do innej operacji (visible do warunku WHERE, a x do sortowania ORDER BY).

Ale można pomyśleć jeszcze w inny sposób:

Rozumowanie nr 3:

CREATE TABLE taba (                   CREATE TABLE tabb (               CREATE TABLE tabc (
  .......................               ......................            ...................
  INDEX (id),                           INDEX (ida),                      INDEX (idb),
  INDEX (visible),                      INDEX (id),                       INDEX (visible),
  INDEX (inta, intb)                    INDEX (visible),                  INDEX (y)
)                                       INDEX (x)                       )
                                      )

Rozumowanie jest podobne jak wyżej z tym, że tutaj w tabeli tabb jeszcze bardziej rozdzieliłem indeksy dając osobne na kolumny ida i id. Chociaż należą one do warunku złączenia ON, to jednak nie są wzięte razem z takiego powodu, że nie należą do jednego wyrażenia ON ale do dwóch różnych, łączących różne tabele.

A pytanie jest takie jak w pierwszym zdaniu. Jak optymalnie nałożyć indeksy na kolumny z tych tabel wiedząc, że zapytanie będzie wyglądało tak jak powyżej? Czy można w tym przypadku łączyć indeksy w takiej kolejności w jakiej występują w zapytaniu czy też, rozdzielić na te grupy indeksów, których nazwy kolumn występują przy różnych klauzulach: jedna grupa na ON, następna grupa indeksu na kolumny z warunku WHERE, a jeszcze inna grupa na kolumny przy ORDER BY.