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->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...

Píše Daniel Milde aka Dundee

Rubriky

• Bezpečnost & Hacking
• Fotografování
• Hardware
• Linux
• O Blogu
• O Mně
• Ostatní
• Paragliding
• Politika
• Programování
• Software
• Webdesign
• Webhosting