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.