Zrobiłem malutki teścik zastosowania indexów na wybranej tabeli w bazie Mysql. Chciałem zobaczyć jakie będą  różnice czasowe zapytań podczas zastosowania różnych indexów na mojej tabeli.

Tabela ma postać:

CREATE  TABLE IF NOT EXISTS `media` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `acl_user_id` INT(11) DEFAULT NULL,
  `parent_id` INT(11) DEFAULT NULL,
  `size` INT(11) DEFAULT NULL,
  `extension` ENUM('mp3','mp4','mpg', 'mpeg', 'jpg', 'png', 'bmp' ) DEFAULT NULL,
  `type`  ENUM('audio','video','image', 'avatar')NOT NULL,
  `status` ENUM('active','inactive','deleted', 'processing', 'banned' ) NOT NULL ,
  `date_created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
  `date_activated` DATETIME DEFAULT NULL,
  `date_deleted` DATETIME DEFAULT NULL,
  `date_banned` DATETIME DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `path` VARCHAR(255) DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL ,
  `guid` CHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_polish_ci;

Użyłem procedury sql do wypełnienia tej tabeli pół milionem losowych danych.

W przypadku braku indexów wypełnianie tabeli danymi  trwało: 4 min 17.78 sec

Czas na pytania bez indexów:

SELECT COUNT(id) FROM media WHERE STATUS = 'active'(trwało: 1.08 sec)
SELECT id FROM media WHERE SIZE = 30;(trwało:  0.99 sec)
SELECT COUNT(id) FROM media WHERE name = 'nazawa'(trwało: 0.93 sec)
SELECT COUNT(id) FROM media WHERE SIZE = 30 AND STATUS = 'active' AND name = 'nazawa'; (trwało: 1.10 sec)

Następnie dodałem następujące indexy:

ALTER TABLE media ADD INDEX idx_media_status (STATUS);
ALTER TABLE media ADD INDEX idx_media_size (`size`);
ALTER TABLE media ADD INDEX idx_media_name (`name`);

(* dodawanie indeksów do istniejących pół miliona wierszy trwało odpowiednio: 18.11 sec, 23.24 sec oraz 39.00 sec)

W przypadku, gdy indexy istniały już w schemacie, napełnienie tabeli o pół mioliona danych trwało: 5 min 13.60 sec

Ponowiłem zapytania z wprowadzonymi indexami. Oto wyniki:

SELECT COUNT(id) FROM media WHERE STATUS = 'active'; (trwało: 0.02 sec)
SELECT id FROM media WHERE SIZE = 30;(trwało: 0.01 sec)
SELECT COUNT(id) FROM media WHERE name = 'nazawa'; (trwało: 0.02 sec)
SELECT COUNT(id) FROM media WHERE SIZE = 30 AND STATUS = 'active' AND name = 'nazawa'; (trwało: 0.10 sec)

Dodałem jeszcze jeden klucz, aby zoptymalizować ostatnie zapytanie, z którego bardzo często będę korzystał w aplikacji:

ALTER TABLE media ADD INDEX idx_media_name_status_size (`name`, STATUS, `size`);

A oto wyniki zapytania:

SELECT COUNT(id) FROM media WHERE SIZE = 30 AND STATUS = 'active' AND name = 'nazawa'; (0.03 sec)

Jak widać z powyższych przykładów stosowanie indexów ma ogromny wpływ na wydajność. Podobne wnioski można zauważyć na wielu blogach, jednak najlepiej wykonać taki prosty test samemu, aby przekonać się o sile stosowania indexów. Pamiętajmy jednak, aby stosować indexy z umiarem – ich wprowadzenie opóźnia zapis danych do bazy. Ponadto, tak jak mówi dokumentacja mysql,  należy zauważyć różnicę  pomiędzy indexem dla jednego pola i dla wielu:

ALTER TABLE media ADD INDEX idx_media_size (`size`);

a

ALTER TABLE media ADD INDEX idx_media_name_status_size (`name`, STATUS, `size`);

są to dwa różne klucze, służące różnym typom zapytań.