MySQL umożliwia zakładanie indeksów na kilka kolumn. To oczywiście nic nowego. Tego typu indeks jest przydatny jeśli w zapytaniu mamy np. warunek WHERE z kilkoma kolumnami. W takiej sytuacji jesteśmy w stanie wykorzystać to, że kombinacja tych warunków pasuje do mniejszej rekordów niż każdy z tych warunków z osobna. Czy tak jest zawsze?

Okazuje się, że nie do końca. Weźmy dwie takie tabele:

*************************** 1. row ***************************
Table: back1
Create Table: CREATE TABLE `back1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tytul` varchar(100) NOT NULL DEFAULT '',
`url` varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM AUTO_INCREMENT=71891 DEFAULT CHARSET=latin2
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE back2\G
*************************** 1. row ***************************
Table: back2
Create Table: CREATE TABLE `back2` (
`id` int(11) NOT NULL DEFAULT '0',
`id_pod` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`id_pod`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2
1 row in set (0.00 sec)

Co jest istotne, to to że klucz główny w tabeli back2 składa się z dwóch kolumn – `id` i `id_pod`

A teraz zapytanie:

mysql> EXPLAIN EXTENDED SELECT COUNT(*) FROM  `back1` k, `back2` p  WHERE k.id = p.id AND p.id_pod = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: k
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 71888
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: p
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: baza.k.id,const
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select count(0) AS `COUNT(*)` from `baza`.`back1` `k` join `baza`.`back2` `p` where ((`baza`.`p`.`id_pod` = 1) and (`baza`.`p`.`id` = `baza`.`k`.`id`))
1 row in set (0.00 sec)

Warunek id_pod=1 jest bardzo wybiórczy:

mysql> SELECT COUNT(*) FROM back2 WHERE id_pod=1;
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.03 sec)

Pomimo tego widzimy że MySQL nie ma ochoty wykorzystać tej kolumny. Co jest grane? Jak przyglądniemy się dokładniej działaniu tego zapytania, odpowiedź jest prosta. MySQL realizuje ten JOIN przez przeglądnięcie kolumny `id` w jednej z tabel (`back1`, bo jest po prostu mniejsza) i dopasowanie rekordów o tym samym `id` z tabeli drugiej. Nawet jeśli, stosując dyrektywę STRAIGHT_JOIN, wymusimy odwrócenie kolejności JOIN’a, to MySQL nie ma fizycznej możliwości aby zlokalizować w tabeli back2 rekordy w których kolumna id_pod ma wartość 1. Kolumna ta jest na drugim miejscu w indeksie i po prostu nie da się na podstawie tego warunku zlokalizować odpowiedniego rekordu. Nadal konieczne jest wykonanie pełnego skanu tabeli `back2` (skan jest robiony po indeksie, więc jest szybszy niż całej tabeli, ale jest to nadal pełny skan) a następnie nałożenie filtra WHERE i wyciągnięcie rekordów pasujących do warunku id_pod=1.

Sytuację zupełnie zmieni dodanie indeksu na kolumnę `id_pod`. Dzięki temu indeksowi MySQL będzie w stanie wyciągnąć tylko te rekordy, które spełniają warunek. Mając te rekordy można zrobić JOIN’a bez konieczności wykonywania skanów indeksów. Zobaczmy zresztą w praktyce jak to wygląda:

mysql> ALTER TABLE back2 ADD INDEX idx_pod (id_pod);
Query OK, 155994 rows affected (0.43 sec)
Rekordów: 155994  Duplikatów: 0  Ostrzeżeń: 0

mysql> EXPLAIN SELECT COUNT(*) FROM  `back1` k, `back2` p  WHERE k.id = p.id AND p.id_pod = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: p
type: ref
possible_keys: PRIMARY,idx_pod
key: idx_pod
key_len: 4
ref: const
rows: 1
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: k
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: baza.p.id
rows: 1
Extra: Using index
2 rows in set (0.00 sec)

Z powyższego wywodu można wyciągnąć prosty wniosek. Trzeba dobrze zastanowić się jakie zapytania są wykonywane w bazie danych. Trzeba też mieć świadomość tego, w jaki sposób działają indeksy w MySQL. Wielokolumnowe indeksy to rzecz bardzo przydatna, ale nie jest to rozwiązanie, które zastąpi “tradycyjne” indeksowanie poszczególnych kolumn. W tym przypadku dodanie indeksu zmniejszyło czas wykonywania zapytania z kilkunastu sekund do ułamka sekundy.