Podczas tworzenia nowych, bądź modyfikowania starych zapytań kluczową kwestią jest sprawdzenie, jak dane zapytanie zachowuje się w różnych warunkach. Dlaczego jest to tak ważne? Weźmy pod uwagę następujące zapytanie:

SELECT first_name, last_name, title FROM film LEFT  OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC;

Podczas przeprowadzania jego analizy (http://blog.ksiazek.info/2010/04/09/benchmark-i-profiling/) okazało się, że przydatny może się okazać indeks nałożony na kolumny first_name i last_name w tabeli `actor`.
Po sprawdzeniu wyników okazuje się, że dodanie tego indeksu nieznacznie zmniejszyło czas wykonywania zapytania (tabela `actor1` zawiera dodatkowy indeks):

|       14 |  0.00054000 | SELECT SQL_NO_CACHE first_name, last_name, title FROM film  LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor1 USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC     |
|       15 | 0.00062100 | SELECT SQL_NO_CACHE  first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING  (film_id) LEFT OUTER JOIN actor1 USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC     |
|        16 | 0.00051900 | SELECT SQL_NO_CACHE first_name, last_name, title FROM  film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor1  USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS'  ORDER BY title DESC     |
|       17 | 0.00047700 | SELECT  SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER JOIN  film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC      |
|        18 | 0.00045100 | SELECT SQL_NO_CACHE first_name, last_name, title FROM  film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN actor  USING (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS'  ORDER BY title DESC      |
|       19 | 0.00046100 | SELECT  SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER JOIN  film_actor USING (film_id) LEFT OUTER JOIN actor USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC      |

To samo okazuje się, gdy wykonamy prosty benchmark przy pomocy skryptów php:
– indeks – 3834.18 zapytań na sekundę
– bez indeksu – 3923.48 zapytań na sekundę

Co się stanie gdy założymy następującą sytuację:
1. znacznie zwiększa nam się liczba rekordów w tabeli actor – z 200 do 530401 rekordów
2. zwiększa się nam ilość rekordów, których kolumna last_name zawiera wartość ‚GUINESS’ – z 3 do 1103 rekordów

|        2 | 0.00056200 | SELECT SQL_NO_CACHE  first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING  (film_id) LEFT OUTER JOIN actor_tmp USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC  |
|         3 | 0.00054100 | SELECT SQL_NO_CACHE first_name, last_name, title FROM  film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN  actor_tmp USING (actor_id) WHERE first_name='PENELOPE' AND  last_name='GUINESS' ORDER BY title DESC  |
|        4 | 0.00061300 |  SELECT SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER  JOIN film_actor USING (film_id) LEFT OUTER JOIN actor_tmp USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC  |
|        6 | 0.00858700 | SELECT SQL_NO_CACHE  first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING  (film_id) LEFT OUTER JOIN actor_tmp1 USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC |
|         7 | 0.00856800 | SELECT SQL_NO_CACHE first_name, last_name, title FROM  film LEFT OUTER JOIN film_actor USING (film_id) LEFT OUTER JOIN  actor_tmp1 USING (actor_id) WHERE first_name='PENELOPE' AND  last_name='GUINESS' ORDER BY title DESC |
|        8 | 0.00861300 |  SELECT SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER  JOIN film_actor USING (film_id) LEFT OUTER JOIN actor_tmp1 USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC |

Struktury tabeli wyglądają następująco:

mysql>  show create table actor_tmp1;
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  Table      | Create  Table                                                                                                                                                                                                                                                                                                                                                                               |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  actor_tmp1 | CREATE TABLE `actor_tmp1` (
`actor_id` bigint(20)  unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT  NULL,
`last_name` varchar(45) NOT NULL,
`last_update`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY  `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB  AUTO_INCREMENT=530402 DEFAULT CHARSET=utf8 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1  row in set (0,00 sec)

mysql> show create table actor_tmp;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  Table     | Create  Table                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  actor_tmp | CREATE TABLE `actor_tmp` (
`actor_id` bigint(20)  unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT  NULL,
`last_name` varchar(45) NOT NULL,
`last_update`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY  `idx_actor_last_name` (`last_name`),
KEY `idx_first_name_last_name`  (`first_name`,`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=530402  DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1  row in set (0,00 sec)

Tym razem widzimy, że zapytanie z dodatkowym indeksem (tabela `actor_tmp`) jest o rząd wielkości szybsze. Potwierdza to też benchmark wykonany z poziomu skryptu php:
– indeks – 3589.85 zapytań na sekundę
– brak indeksu – 117.76 zapytań na sekundę

Dlaczego tak się stało?

mysql> EXPLAIN  SELECT SQL_NO_CACHE first_name, last_name, title FROM film LEFT OUTER  JOIN film_actor USING (film_id) LEFT OUTER JOIN actor_tmp1 USING  (actor_id) WHERE first_name='PENELOPE' AND last_name='GUINESS' ORDER BY  title DESC;
+----+-------------+------------+--------+-----------------------------+----------------+---------+----------------------------+------+--------------------------+
|  id | select_type | table      | type   | possible_keys               |  key            | key_len | ref                        | rows |  Extra                    |
+----+-------------+------------+--------+-----------------------------+----------------+---------+----------------------------+------+--------------------------+
|   1 | SIMPLE      | film       | index  | PRIMARY                     |  idx_title      | 767     | NULL                       |  257 | Using  index              |
|  1 | SIMPLE      | film_actor | ref    |  PRIMARY,idx_fk_film_id      | idx_fk_film_id | 2       |  sakila.film.film_id        |    3 | Using where; Using index |
|  1 |  SIMPLE      | actor_tmp1 | eq_ref | PRIMARY,idx_actor_last_name |  PRIMARY        | 8       | sakila.film_actor.actor_id |    1 | Using  where              |
+----+-------------+------------+--------+-----------------------------+----------------+---------+----------------------------+------+--------------------------+
3  rows in set (0,00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE  first_name, last_name, title FROM film LEFT OUTER JOIN film_actor USING  (film_id) LEFT OUTER JOIN actor_tmp USING (actor_id) WHERE  first_name='PENELOPE' AND last_name='GUINESS' ORDER BY title DESC;
+----+-------------+------------+--------+------------------------------------------------------+--------------------------+---------+---------------------------+------+-----------------------------------------------------------+
|  id | select_type | table      | type   |  possible_keys                                        |  key                      | key_len | ref                       | rows |  Extra                                                     |
+----+-------------+------------+--------+------------------------------------------------------+--------------------------+---------+---------------------------+------+-----------------------------------------------------------+
|   1 | SIMPLE      | actor_tmp  | ref    |  PRIMARY,idx_actor_last_name,idx_first_name_last_name |  idx_first_name_last_name | 274     | const,const               |    1 |  Using where; Using index; Using temporary; Using filesort |
|  1 |  SIMPLE      | film_actor | ref    |  PRIMARY,idx_fk_film_id                               |  PRIMARY                  | 2       | sakila.actor_tmp.actor_id |   13 |  Using where; Using index                                  |
|  1 |  SIMPLE      | film       | eq_ref |  PRIMARY                                              |  PRIMARY                  | 2       | sakila.film_actor.film_id |    1  |                                                           |
+----+-------------+------------+--------+------------------------------------------------------+--------------------------+---------+---------------------------+------+-----------------------------------------------------------+
3  rows in set (0,00 sec)

Zmienił się kompletnie plan zapytania. Optimizer MySQL uznał, że przy braku indeksu lepszym rozwiązaniem będzie wyjście od tabeli `film`, a nie `actor_tmp`, jak to jest w przypadku gdy mamy nałożony indeks na kolumny ‚first_name’ i ‚last_name’. Wzięło się to z tego, że wedle szacunków, przy braku indeksu, konieczne byłoby sprawdzenie 1103 rekordów w tabeli actor_tmp1 – dlatego lepiej zacząć od tabeli `film` – tu do sprawdzenia jest 257 rekordów.

Jak widać, nie wystarczy przetestować wydajność zapytań na podstawowym zestawie danych. Trzeba zastanowić się jak docelowo wyglądać będzie baza – wielkość, ilość rekordów, czy zawartość poszczególnych kolumn będzie unikalna, a jeśli nie to jak dużo będzie powtórzeń? Jeśli baza danych ma działać z optymalną wydajnością, to testy i analizę zapytań dobrze jest ponawiać co pewien czas. Może się okazać, że to co było optymalne 500 tysięcy rekordów temu w tym momencie optymalne już nie jest – choćby z takiego powodu, że większa ilość danych przestała się mieścić w pamięci i teraz każdy rekord, który jest sprawdzany to operacja dyskowa. Powyższe testy wykonywane były na malutkim VPSie z mocno ograniczonymi zasobami pamięci. Ta sama baza i te same zapytania uruchomione na serwerze fizycznym, w przypadku którego z pamięcią nie ma problemów – okazuje się, że indeks praktycznie nie wpływa na zmianę szybkości działania zapytania.