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.