To, że indeksy są niezbędne do poprawnego funkcjonowania bazy danych, powinni wiedzieć nie tylko administratorzy MySQL, ale także cała reszta użytkowników, którzy projektują strukturę bazy danych.

W silnikach MyISAM i InnoDB stosowane są indeksy typu B-tree. W pewnym uproszczeniu. W praktyce, pomiędzy indeksem w MyISAM a indeksem w InnoDB jest kilka różnic, nie są one jednak istotne dla dalszej części tego postu. Indeksy mogą zostać nałożone na jedną, lub kilka kolumn. Jest to stosunkowo istotna ich cecha, o której twórcy aplikacji bazodanowych (szczególnie ci początkujący) nie zawsze pamiętają. Przykładowy ALTER TABLE dodający indeks na kolumny `kategoria`, `podkategoria` i `cena` w tabeli `produkt` mógłby wyglądać następująco:

ALTER TABLE produkt ADD INDEX idx_kat_podkat_cena (kategoria, podkategoria, cena);

Jak dokładnie działają tego typu indeksy, można dowiedzieć się z Google, natomiast dla administratora MySQL najważniejsze jest to, że można je zastosować w następujących przypadkach.

Pełne dopasowanie – indeks pomoże w znalezieniu rekordów, których kategoria to „artykuły papiernicze”, podkategoria to „zeszyty” a cena wynosi 10 złotych.

SELECT * FROM produkt WHERE kategoria='artykuły papiernicze' AND podkategoria='zeszyty' AND cena=10;

W tego typu zapytaniu wykorzystywane są wszystkie kolumny indeksu.

Zapytanie może dotyczyć też tylko kolumny, która jest pierwsza od lewej. W ten sposób możemy wykorzystać indeks do wylistowania rekordów, których kategoria to „wyroby papiernicze”.

SELECT * FROM produkt WHERE kategoria='artykuły papiernicze';

Takie zapytanie wykorzystuje tylko pierwszą kolumnę indeksu

Podobnie z zapytaniem o kategorie, które np. zaczynają się na literę ‚a’.

SELECT * FROM produkt WHERE kategoria LIKE 'a%';

Indeks potrafi także pomóc w przypadku zapytania, które wyszukuje rekordy o konkretnej kategorii, których podkategoria zaczyna się na jakiś ciąg znaków:

SELECT * FROM produkt WHERE kategoria='artykuły piśmiennicze' AND podkategoria LIKE 'pi%';

Rozpatrywany indeks może także zostać wykorzystany do wyszukania rekordów, których kategoria znajduje się pomiędzy dwoma konkretnymi. Tu akurat takie wyszukiwanie jest trochę nieprzydatne, ale już na przykład w spisie pracowników wyciągnięcie osób, których nazwiska są pomiędzy Kowalskim a Nowakiem może się do czegoś przydać. Dodatkowo, należy wspomnieć także o tym, że indeksy przechowują dane w sposób posortowany. W związku z tym są wykorzystywane także i do sortowania wyników poleceniem ORDER BY;

Oczywiście, indeksy B-tree posiadają także pewne ograniczenia. Są one głównie związane z „lewostronnością” takiego indeksu. Przykładowo, jeśli w zapytaniu mamy warunek dotyczący pierwszej i trzeciej kolumny w indeksie, to w praktyce wykorzystanie indeksu ograniczy się do pierwszej kolumny. W zapytaniu nie ma warunku dotyczącego drugiej kolumny, tak więc brakuje ciągłości wykorzystania indeksu. Podobnie, jeśli w drugiej kolumnie mamy dopasowanie z wildcardem. W zapytaniu

SELECT * FROM produkt WHERE kategoria='artykuły piśmiennicze' AND podkategoria LIKE 'pi%' AND cena=10;

jest co prawda warunek dotyczący drugiej kolumny, ale nie ma pełnego dopasowania. W związku z tym, nie ma możliwości skorzystania z trzeciej kolumny – zostaną wykorzystane tylko dwie pierwsze. Analogicznie, jeśli zapytanie nie ma warunku dotyczącego pierwszej kolumny, to nie ma możliwości skorzystania z naszego przykładowego indeksu.

Dla administratora MySQL wniosek z powyższego jest prosty – konieczne jest dokładne pilnowanie jakie indeksy są faktycznie konieczne. Warunki w zapytaniach mogą być różne. Może się okazać, że jeden indeks nie wystarczy aby wszystkie z zapytań mogły z indeksu skorzystać.

ALTER TABLE produkt ADD INDEX idx_kat_podkat_cena (kategoria, podkategoria, cena);
ALTER TABLE produkt ADD INDEX idx_cena_kat_podkat (cena, kategoria, podkategoria);
ALTER TABLE produkt ADD INDEX idx_podkat_kat_cena (podkategoria, kategoria, cena);

Powyższe indeksy, choć nałożone na te same kolumny, to w praktyce trzy różne indeksy. Może się okazać, że zapytania do tabeli produkt mają tak skonstruowane warunki, że konieczne będzie nałożenie wszystkich trzech aby każde z zapytań mogło z indeksu skorzystać. Należy także pamiętać, że zaindeksowanie dwóch kolumn dwoma, pojedynczymi indeksami to nie to samo co nałożenie jednego indeksu na obie kolumny.

ALTER TABLE produkt ADD INDEX idx_kat (kategoria);
ALTER TABLE produkt ADD INDEX idx_podkat (podkategoria);

to nie to samo co:

ALTER TABLE produkt ADD INDEX idx_kat_podkat (kategoria, podkategoria);

Zapytanie typu:

SELECT * FROM produkt WHERE kategoria='artykuły papiernicze' AND podkategoria='zeszyty';

wydajniejsze będzie, jeśli będzie mogło skorzystać z indeksu na obie kolumny.