k47.cz    — každý den dokud se vám to nezačne líbit
foto Praha výběr povídky kultura | twitter FB


Kdy vám SQL_CALC_FOUND_ROWS zabije databázi

publikováno 29. 8. 2012 od k47

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í:

- limit/offset stránkování nemusí být pomalé
- Střeva databází


vstoupit do diskuze    sdílet na facebooku, twitteru, google+

příbuzné články:
MySQL: rychlý výběr náhodného záznamu 📷
Hromadný update v jednom SQL dotazu
Pravidelná dvojrozměrná data v MySQL
MySQL group by trik
Projekt Chanminer - implementace
Konvertor exportů návštěvních knih Blueboard do SQL

sem odkazují:
Radši nevědět
Útok /prog/

píše k47 & hosté, kontakt: ascii@k47.cz