Získání počtu řádek

Školení, která pořádám

Článek vyšel v rámci PHP okénka na serveru Root.cz.

Často je potřeba získat počet řádek, které obsahuje databázová tabulka. A jako obvykle k tomu vede více cest.

<?php
// neefektivní
$pocet = mysql_num_rows(mysql_query("SELECT * FROM tabulka"));

// rychlejší
$pocet = mysql_result(mysql_query("SELECT COUNT(*) FROM tabulka"), 0);
?>

První způsob je neefektivní proto, že SQL server se připraví na to, že bude vracet všechna data z tabulky a připraví si na to potřebné struktury se všechna data přenesou z databázového serveru a PHP je uloží do paměti. Druhý způsob je naproti tomu velice rychlý, protože MySQL jenom vezme počet řádků tabulky, který si vede jako statistický údaj, a vrátí ho. Funkci mysql_num_rows je vhodné použít pouze v případě, kdy data z tabulky budeme tak jako tak potřebovat, tedy např.:

<?php
$result = mysql_query("SELECT * FROM tabulka");
echo "Celkový počet záznamů: " . mysql_num_rows($result) . "\n";
while ($row = mysql_fetch_assoc($result)) {
    // zpracování tabulky
}
mysql_free_result($result);
?>

Funkci mysql_num_rows je vhodné použít i v případě, kdy chceme zjistit, zda dotaz vůbec něco vrátil:

<?php
// čitelný a dostatečně efektivní způsob
$result = mysql_query("SELECT * FROM tabulka");
if (!mysql_num_rows($result)) {
    echo "Tabulka neobsahuje žádná data.\n";
} else {
    while ($row = mysql_fetch_assoc($result)) {
        // zpracování tabulky
    }
}
mysql_free_result($result);

// krajně nečitelný zápis
$result = mysql_query("SELECT * FROM tabulka");
$row = mysql_fetch_assoc($result);
if (!$row) {
    echo "Tabulka neobsahuje žádná data.\n";
} else {
    do {
        // zpracování tabulky
    } while ($row = mysql_fetch_assoc($result));
}
mysql_free_result($result);
?>

Další problém je potřeba vyřešit v případě, kdy sice chceme zjistit celkový počet řádků tabulky, ale chceme jich vypsat jen několik.

<?php
// pracné
$pocet = mysql_result(mysql_query("SELECT COUNT(*) FROM tabulka /* WHERE složitá podmínka */"), 0);
$result = mysql_query("SELECT * FROM tabulka /* WHERE složitá podmínka */ LIMIT $limit OFFSET $offset");
while ($row = mysql_fetch_assoc($result)) {
    // zpracování řádku
}
mysql_free_result($result);

// neefektivní
$result = mysql_query("SELECT * FROM tabulka /* WHERE složitá podmínka */");
$pocet = mysql_num_rows($result);
if ($offset < $pocet) {
    mysql_data_seek($result, $offset);
    $i = 0;
    while ($row = mysql_fetch_assoc($result)) {
        // zpracování řádku
        $i++;
        if ($i >= $limit) {
            break;
        }
    }
}
mysql_free_result($result);

// elegantní, ale jde použít pouze v MySQL a to až od verze 4
$result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM tabulka /* WHERE složitá podmínka */ LIMIT $limit OFFSET $offset");
$pocet = mysql_result(mysql_query(" SELECT FOUND_ROWS()"), 0);
while ($row = mysql_fetch_assoc($result)) {
    // zpracování řádku
}
?>

První způsob je neefektivní pro dotazy se složitými podmínkami (např. vyhledávání podle masky) nebo pracný pro dotazy složitě spojující více tabulek, druhý způsob je neefektivní pro vysoký $offset. To, že je úloha získání celkového počtu řádků společně s vrácením jen několika z nich poměrně častá, si v MySQL uvědomili a nabídli pro ni elegantní, byť nestandardní řešení. Pokud ale aplikace nemusí pracovat nad více databázemi zároveň, není důvod se mu bránit. Toto řešení bohužel zpracovává všechny výsledky dotazu, takže není příliš výkonné.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Dobře míněné rady, 30.3.2005, diskuse: 23 (nové: 0)

Diskuse

Hellish:

Chtěl bych požádat, zda-li by nebylo možné ty články, které vycházejí na rootu, třeba i s nějakým zpožděním umisťovat i na tento web. Když jsou informace na jednom místě, lépe se v nich hledá apod. Díky

ikona Jakub Vrána OpenID:

Ony tu jsou, ale zakomentované v HTML :-). Interní vyhledávání v nich zde na serveru funguje, externí pochopitelně ne. Články jsem sem nedával hlavně kvůli diskusím - lepší je jedna centrální diskuse než dvě s opakujícími se tématy.

Ale asi máš pravdu, články tady s nějakým zpožděním uveřejním, od redakce to mám povolené.

24k:

Super, posledni dobou spojuji nekolik tabulek zaroven, pouzivam subselecty, klauzuli UNION a dost casto limituji vypis radku a proto jsem muzel slozity SQL zopakovat, takhle to je hracka! :)

Knedle:

cim nahradim mysql_result v php5 pomoci mysqli? nejak se nemuzu dopatrat...

Mark:

Muzete prosim nekdo odpovedet na Vyse zmineny dotaz. Zkousel jsem spoustu moznosti a zadna mi nefunguje, taky se m si vsiml, ze se tento dotaz objevuje na vice mistech a nikdo zatim na nej nezareagoval, ze by snad nemel reseni??? dekuji

ikona Jakub Vrána OpenID:

V MySQLi obdoba této funkce není.

Tomáš:

Lze také vypsat čísla jednotlivých řádků v seznamu?

mat:

bohužel pokud se pracuje s velkými tabulkami (miliony záznamů) navíc s joiny, vychází SQL_CALC_FOUND_ROWS několikanásobně pomaleji než použití dvou dotazů (jeden s limitem pro získání dat a druhý s count(*) pro získání celkového počtu)

peta:

no jo, jenže když spojíš víc tabulek a máš složitější dotaz, tak count(*) nefunguje správně. (aspoň mě to vyhazovalo špatnej výsledek..)

ikona Jakub Vrána OpenID:

Uveď konkrétní dotaz, problém bude spíš v něm.

ikona Jakub Vrána OpenID:

Viz http://www.mysqlperformanceblog.com/2007/…_found_rows/

ikona merlin:

Jak byste převdl tento princip do MySQLi?

Zdenda:

Tisíceré díky za tento článek, jako vždy mi to pomohlo. Vůbec nechápu, jak jsem bez toho mohl do teď žít! :-)

Flexa:

Měl bych dotaz... Co dělat, když join-uji tabulky vazby M:N, takže ve výsledku jeden záznam se vrátí ve více řádcích, které následně zpracuji v php, takže počet záznamů vlastně nezískám jinak, než ve dvou krocích. Je snad nějaký způsob, jakým je spojit ještě na sql serveru v jeden "sloupec"?

ikona Jakub Vrána OpenID:

Ano, jmenuje se agregace. http://php.vrana.cz/group-by.php

Flexa:

Za radu děkuji, ale buďto ji neumím použít správě, nebo to není v tomto případě vhodné. Stane se to, že z tabulky (M:N) vrátí jen jeden záznam ze všech... Zatím jsem zůstal u dvou dotazů.

Ferda:

bohuzel se zavery tohoto clanku (zejmena z posledniho odstavce, ze ktereho neprimo jako "nejfektivnejsi" rozumej nejlepsi vychazi SQL_CALC_FOUND_ROWS) nelze souhlasit. Jak jiz bylo ukazano na zminenem blogu

http://www.mysqlperformanceblog.com/2007/…_found_rows/

pokud jsou spravne udelane indexy, je cesta COUNT() vyrazne rychlejsi nez SQL_CALC_FOUND_ROWS, slozitost podminky s tim vcelku nema co delat, protoze stale se jedna o to, ze ta podminka musi byt postavena nad dobre oindexovanymi tabulkami, pokud ne, ani eliminace jednoho dotazu to nespasi. Samozrejme pro nejake smudlani s par zaznamy to nezmeni nic, a je to ciste o pohodlnosti dat to do jednoho zaznamu, ale pokud se bavime o tabulkach v radu tisicu a statisicu zaznamu (prumerne diskusni forum treba), tak tam uz to hraje roli. A presne k tomuto zaveru jsem dospel po zkoumani, proc mam v mySQL ve statistikach za dva mesice behu pres 598 miliard Handler_read_rnd_next, tedy pozadavku na precteni dalsiho radku z tabulky. Tabulky oindexovane, dotazy svizne, jen tohle cislo prilis vysoke. A staci se podivat na explain plany dotazu s SQL_CALC_FOUND_ROWS v tom vyse uvedenem odkazu treba, a je jasne, ze misto par set zaznamu "finalniho" vysledku tahle stupidita skenuje celou zdrojovou tabulku, popripade join ... No a to samozrejme naskakuje, kdyz mate tisic zaznamu v jedny, par set tisic zaznamu v druhy, a to se skenuje porad dokola jen proto, ze nejaky programator pisici prihlouple forum, ktere si bohuzel uzivatele za predchoziho majitele oblibili, byl liny udelat dva dotazy a propadl presne takoveto zkreslujici predstave, ze SQL_CALC_FOUND_ROWS je rychlejsi, protoze se preci vykonava jen jednou ! Doporucuji tedy napsat si kod s obema variantami a zatizit to testem, abyste si porovnali, jestli to pro vas konkretni projekt skutecne vyhodnejsi JE, nebo NENI. Nic neni cernobile, a ve svete IT neexistuje "jedine spravne" reseni :)

ikona Jakub Vrána OpenID:

Díky za opravení, je to skutečně tak, jak popisuješ. Článek jsem upravil.

Podle mě by to měl řešit optimalizátor MySQL, protože efektivní způsob, jak celkový počet řádek zjistit, existuje. Ale bohužel to zatím neřeší.

delphista:

zdravim....malý dotaz,
jak by to to bylo efektivně, když dotaz vrátí  dvěste řadků výsledkua já chci zobrazit druhých dvacet řádku?

Díky. delphista

ikona Jakub Vrána OpenID:

Nejlepší je dotaz položit tak, aby vrátil jen těch 20 řádek. V některých databázích se k tomu používají klauzule LIMIT a OFFSET.

msx:

Síce starý článok, ale z vlastnej skúsenosti musím povedať, že ešte rýchlejšie je toto:

select count(id) from tabulka

Raz na jednej stránke som zámenou hviezdičky za názov jedného stĺpca zrýchlil načítanie z pôvodných 2 sekúnd na necelú pol sekundu.

joel:

Zdravím, rád bych požádal o návrh řešení....
dotazem (SELECT něco) získám množinu záznamů M1. Použitím (SELECT něco LIMIT n,m) získám libovolnou podmnožinu z M1. Nyní jde o to, jak získat číslo konkrétního záznamu z M1 splňujícího určitou podmínku (např. id_user = 1000), přičemž číslem záznamu mám na mysli interní identifikátor který používá klauzule LIMIT. Potřebuji to použít pro sestavení dotazu (SELECT něco LIMIT n,m) na základě výsledku vyhledávání abych mohl vypsat správnou stránku záznamů obsahující zmíněný záznam s určitou podmínkou.
Fuj, doufám, že je to srozumitelné...

Díky

ikona Jakub Vrána OpenID:

Viz http://php.vrana.cz/odkaz-na-predchozi-a-dalsi-zaznam.php.

Diskuse je zrušena z důvodu spamu.

avatar © 2005-2024 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.