MySQL: Rychlost UPDATE sloupce v závislosti na přítomnosti indexů

Nedávno jsme s Lukášem Churým a Ondrou Vašíčkem řešili rychlost operace UPDATE v závislosti na tom, jestli upravovaný sloupec má či nemá nastavený index.

Představte si, že máme v MySQL 5.0 tabulku o několika sloupcích, dva z nich jsou „id“ a „xy“. Nad sloupcem „id“ je přidělený index. Sloupec „xy“ index nemá. Rychlost UPDATE sloupce „xy“ trvá nějaký čas.

Otázka zní, jestli UPDATE zabere stejný čas i v případě, kdyby sloupec „xy“ měl přidělený index.

Udělal jsem jednoduchý test, který přináší odpověď.

Tabulka, sloupce, indexy a hodnoty:

• mám tabulku (úložiště typu MyISAM), která má 10 sloupců
• jeden sloupec je bez indexů (sloupec „xy“)
• dva sloupce mají unikátní společný index
• zbývající sloupce mají po jednom indexu
• v tabulce je 10 000 záznamů
• ve sloupci „xy“ (nemá index!) jsou stejné hodnoty „1“ (INT)

Následně jsem pomocí UPDATE změnil hodnoty sloupce „xy“ z 1->0, případně 0->1. Operace vždy změnila všechny řádky tabulky, tedy 10 000 řádků.

UPDATE `test` SET `xy`=0 WHERE `xy`=1

Výsledky měření:

• Operace UPDATE nad sloupcem „xy“, který nemá index, trvá 0.0540 s
• Operace UPDATE nad sloupcem „xy“, který má index, trvá 0.0997 s
• Pokud zruším všechny indexy v tabulce, operace UPDATE trvá 0.0419 s

Závěr:

• Pokud aktualizuji sloupec bez indexu, je to rychlé.
• Pokud sloupec má index, operace trvá déle (v tomto případě téměř 2× déle, než u sloupce bez indexu).
• Pokud v tabulce není žádný index, aktualizace je nejrychlejší.

Test jsem provedl na následující konfiguraci:

Apache/2.2.11 (Win32) PHP/5.2.9-2
Verze MySQL klienta: 5.0.67
Rozšíření PHP: mysql

Pokud se mýlím, opravte mě, prosím.