Kdy vám SQL_CALC_FOUND_ROWS zabije databázi
Jiří Knesl píše jak jednoduše zrychlit stránkování v MySQL pomocí speciálního modifikátoru SQL_CALC_FOUND_ROWS, který zjistí kolik záznamů by dotaz vrátil, kdyby neobsahoval LIMIT a OFFSET. Problém je, že tato technika nefunguje jako zázračný všelék a existují případy, kdy jsou dva samostatné dotazy rychlejší než jeden s SQL_CALC_FOUND_ROWS.
Ve stručnosti:
Když dělám stránkování, obvykle chci znát jednu stránku dat a celkový počet
záznamů. Toho se dá jednoduše zařídit dvěma dotazy: jeden s spočítá count(*) a druhý vytahá data omezená limit/offset.
Další možností je použít už zmíněný SQL_CALC_FOUND_ROWS:
SELECT SQL_CALC_FOUND_ROWS * FROM table_x WHERE column_a = a AND column_b = b ORDER BY column_c LIMIT x OFFSET y
Následující volání:
SELECT FOUND_ROWS() as pocet_zaznamu
pak počet všech řádků bez toho, aby bylo třeba podkládat další dotaz do databáze. Výsledek je stejný jako:
SELECT count(*) FROM table_x WHERE column_a = a AND column_b = b
SQL_CALC_FOUND_ROWS začne výkonem pokulhávat, když jsou všechny sloupce uvedené v klauzulích WHERE a ORDER BY obsaženy v indexu. Pak jsou dva dotazy rychlejší.
V takovém případě count(*) dotaz čte data pouze z indexu a potřebuje přečíst
jenom jeden spojitý úsek omezený podmínkou (indexy můžeme považovat za spojitý
blok paměti), což je velice rychlé. Dotaz s limit/offset nemusí přečíst
všechna data vymezená WHERE podmínkou, seřadit je a vybrat několik málo záznamů
které nás zajímají, ale protože je jak podmínka tak i řazení pokrytá indexem, může
rovnou číst data bez řazení do dočasné tabulky (a když máme InnoDB a clusterované indexy, pak jsou data řazena ve stejném pořadí jako index a můžeme je číst
ještě rychleji; na druhou stranu se ani v jednom případě neubráníme neřestem offsetu a indexů v B-stromech, kdy databáze nemůže offset jednoduše přeskočit, ale musí přečíst všechna
data po cestě).
Naproti tomu SQL_CALC_FOUND_ROWS musí vrátit počet řádků a zároveň data a na to si nevystačí s indexem. Proto musí všechna data přečíst, seřadit do dočasné tabulky a vybrat řez dat ze začátku tabulky. To nevadí, když dotaz není pokrytý indexy, protože by se tohle všechno stejně muselo dělat, ale když má k dispozici indexy, je to výrazně pomalejší.
Takže: SQL_CALC_FOUND_ROWS není zlatý grál, tím jsou jedině indexy.
Relevantní čtení: