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

Kdy vám SQL_CALC_FOUND_ROWS zabije databázi

29. 8. 2012 (před 7 lety) — k47 (CC by) (♪)

Jiří Knesl píše jak jed­no­duše zrych­lit strán­ko­vání v MySQL pomocí spe­ci­ál­ního mo­di­fi­ká­toru SQL_CALC_FOUND_ROWS, který zjistí kolik zá­znamů by dotaz vrátil, kdyby ne­ob­sa­ho­val LIMIT a OFFSET. Pro­blém je, že tato tech­nika ne­fun­guje jako zá­zračný všelék a exis­tují pří­pady, kdy jsou dva sa­mo­statné dotazy rych­lejší než jeden s SQL_CALC_FOUND_ROWS.


Ve struč­nosti:

Když dělám strán­ko­vání, ob­vykle chci znát jednu stránku dat a cel­kový počet zá­znamů. Toho se dá jed­no­duše za­ří­dit dvěma dotazy: jeden s spo­čítá count(*) a druhý vytahá data ome­zená 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á­sle­du­jící volání:

SELECT FOUND_ROWS() as pocet_zaznamu

pak počet všech řádků bez toho, aby bylo třeba pod­klá­dat další dotaz do da­ta­báze. Vý­sle­dek je stejný jako:

SELECT count(*) FROM table_x
WHERE column_a = a AND column_b = b

SQL_CALC_FOUND_ROWS začne vý­ko­nem po­kul­há­vat, když jsou všechny sloupce uve­dené v klau­zu­lích WHERE a ORDER BY ob­sa­ženy v indexu. Pak jsou dva dotazy rych­lejší.

V ta­ko­vém pří­padě count(*) dotaz čte data pouze z indexu a po­tře­buje pře­číst jenom jeden spo­jitý úsek ome­zený pod­mín­kou (indexy můžeme po­va­žo­vat za spo­jitý blok paměti), což je velice rychlé. Dotaz s limit/offset nemusí pře­číst všechna data vy­me­zená WHERE pod­mín­kou, se­řa­dit je a vybrat ně­ko­lik málo zá­znamů které nás za­jí­mají, ale pro­tože je jak pod­mínka tak i řazení po­krytá in­de­xem, může rovnou číst data bez řazení do do­časné ta­bulky (a když máme InnoDB a cluste­ro­vané indexy, pak jsou data řazena ve stej­ném pořadí jako index a můžeme je číst ještě rych­leji; na druhou stranu se ani v jednom pří­padě ne­u­brá­níme neřes­tem off­setu a indexů v B-stro­mech, kdy da­ta­báze nemůže offset jed­no­duše pře­sko­čit, ale musí pře­číst všechna data po cestě).

Na­proti tomu SQL_CALC_FOUND_ROWS musí vrátit počet řádků a zá­ro­veň data a na to si ne­vy­stačí s in­de­xem. Proto musí všechna data pře­číst, se­řa­dit do do­časné ta­bulky a vybrat řez dat ze za­čátku ta­bulky. To nevadí, když dotaz není po­krytý indexy, pro­tože by se tohle všechno stejně muselo dělat, ale když má k dis­po­zici indexy, je to vý­razně po­ma­lejší.

Takže: SQL_CALC_FOUND_ROWS není zlatý grál, tím jsou jedině indexy.


Re­le­vantní čtení:

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