Optimalizace počtu dotazů do MySQL

Možná už jste se někdy setkali s tím, že máte menu, které je hierarchicky členěno. Prostě obyčejný rozklikávací pavouk. Bývá to nejčastěji u e-shopů, ale můžeme se s tím setkat i jinde.

Já jsem při vývoji e-shopu narazil na jeden problém:

Generování menu a zjišťování podkategorií otevřené kategorie jsem prováděl pomocí rekurzivních funkcí, které neukládaly žádné mezihodnoty a při každém průchodu použili alespoň jeden dotaz do databáze. Podíval jsem se pak na celkový počet dotazů při jednom reloadu a mírně jsem strnul. Téměř 30 dotazů do DB, přičemž jsem pouze vykreslil menu katalogu a zobrazil příslušné produkty a fotky k nim. To mi přišlo až příliš. Přece jen, když má hosting omezení 20 000 dotazů za hodinu, tak při slušné návštěvnosti by mohly záčít být problémy.

Zaměřil jsem se na funkci, která vrací v poli idčka všech podkategoríí a pokusil jsem se ji zoptimalizovat.

Původní funkce vypadá takto:

function subcategories($id){
  $query = new Query("SELECT `id_category` FROM `".table_name('category')."` WHERE `parent` = '".sql_in($id,1)."'");
  if($query->num()){
    while($sub = $query->fetch()){
      $arr[] = $sub['id_category'];
      $subsub = subcategories($sub['id_category']);
      if(is_array($subsub)){
        $arr = array_merge($arr,$subsub);
      }
    }
    return $arr;
  }else return false;
}
$query->num() vrací počet řádků

$query->fetch() vrací mysql_fetch_array

U trochu rozlehlejšího stromu kategorií začíná dotazů do DB nemile přibývat...

Zkusil jsem tedy funkci napsat jinak a obě metody poté porovnal.

druhý postup

U druhého postupu jsem dbal na co nejmenší počet dotazů do DB - tedy pouze jeden. Zbytek už musí zařídit PHP. Vypadá to takto:

$query = new Query("SELECT id_category,parent FROM `".table_name('category')."`");
while($cat = $query->fetch()){
  $categories[] = array('id'=>$cat['id_category'],'parent'=>$cat['parent']);
}

function subcategories2($id){
  global $categories;
  for($i=0;$i<count($categories);$i++){ 
    if($categories[$i]['parent']==$id){
      $arr[] = $categories[$i]['id'];
      $subsub = subcategories2($categories[$i]['id']);
      if(is_array($subsub)){
        $arr = array_merge($arr,$subsub);
      }
    }
  }
  return $arr;
}

V cyklu jsem musel místo foreach použít for, protože ukazatel na aktuální prvek v poli(u foreach) je nejspíš globální proměnná - po průchodu celého pole ve vnitřní funkci a skoku nahoru do funkce rodičovské cyklus foreach už nepokračoval.

Porovnání

prvni:0.00181913375854
druhy:0.00172901153564

prvni:0.00146102905273
druhy:0.0017831325531

prvni:0.00127506256104
druhy:0.00156593322754

prvni:0.00109505653381
druhy:0.0014660358429

prvni:0.00424790382385
druhy:0.00165700912476

prvni:0.00142979621887
druhy:0.00140404701233

prvni:0.00146007537842
druhy:0.00148797035217

prvni:0.00097393989563
druhy:0.000872850418091

prvni:0.00133800506592
druhy:0.00151610374451

prvni:0.000968217849731
druhy:0.000833034515381

Jak vidíte, obě funkce jsou rychlostně velmi podobné. Zkusil jsem tedy ještě 1000x opakování obou funkcí:

prvni:14.92943573
druhy:8.41595482826

Tady už je rozdíl celkem znatelný. Optimalizovaná funkce tedy nejen že neplýtvá dotazy do DB, jejichž počet může být omezen, ale je dokonce i rychlejší!

Díky této jediné zoptimalizované funkci se mi počet dotazů snížil na polovinu...

evaluation

Komentáre

[1] Pip
2007-02-19 23:59:31

Je nějaký jednoduchý postup jak zjistit počet dotazů do DB při průchodu scriptem? Rád bych zkusil dle článku také optimalizovat, tak jen jestli by šlo poradit nějakou již prověřenou metodu. Děkuji a přeji prima den.

[2] Dundee
2007-02-20 23:34:47

Já jsem to udělal takto: mám třídu, která se stará o komunikaci s databází a v této třídě je metoda Query. Ta se stará o provedení SQL dotazu. Do této metody jsem přidal řádek

$_SESSION['i']++;

ale může to být i GET, POST, nebo REQUEST. Prostě nějaká superglobální proměnná. Na začátku skriptu si ji pro jistotu vynuluju a na konci si ji nechám vypsat..

echo $_SESSION['i'];

Jednoduché a účinné...

[3] Pip
2007-02-25 11:36:39

Děkuji mnohokrát za odpověď. Nakonec jsem to udělal obdobně, tak jsem rád, že jsem se v tom shodli :) Počet dotazů jen uchovávám přímo v proměné SQL třídy.

comments closed