Optimising page processing by creating advanced SQL query

Question

Hi there. I have this weird code on site. It gets all records (order by pozycja), then it creates some kind of stack and limits shown records to 10. However if there are over 200 records, it takes too long, because it's processing whole table, instead of 10.

Values used:

$int_ilosc_na_strone = 10;
$liczba_wierszy = 200;
Podzial_na_podstrony::get_limit_object($int_ilosc, $int_ilosc_na_strone) = 10;
$order = NULL;


public function produkty_kat($adres, $liczba_wierszy, $int_ilosc_na_strone) {
        require_once('lib/Podzial_na_podstrony.class.php');

        $rozloz = explode("/", $adres);
        $id = $rozloz[2];

        $order = $this->podaj_order($adres, 'kategorie');
        if (empty($order)) {
            $order = 'produkty.pozycja ASC';
        }
        if ($order=='price') {
            $order = "produkty.cena ASC";
        }
            $firstack = $this->database->pobierz("SELECT id FROM subkategorie WHERE kategorie_id = '$id' ORDER BY pozycja");
            foreach($firstack as $firstack)
            {
                $stack = $this->database->pobierz("SELECT id FROM sub_subkategorie WHERE subkategorie_id = '{$firstack['id']}' ORDER BY pozycja");
                foreach($stack as $stack)
                {
                    $prods[] = $this->database->pobierz("SELECT produkty.id FROM produkty, przyporzadkowania, stany_magazynowe, gk_grupy_produkty WHERE stany_magazynowe.produkty_id=produkty.id AND produkty.id=przyporzadkowania.produkty_id AND przyporzadkowania.sub_subkategorie_id={$stack['id']} AND produkty.widoczny='1' AND produkty.id = gk_grupy_produkty.id_produktu AND gk_grupy_produkty.id_grupy=$this->int_id_grupy AND gk_grupy_produkty.towar_widocznosc=1 GROUP BY produkty.pozycja ORDER BY $order");
                }
                $temp = array();
                foreach($prods as $o)
                {
                    foreach($o as $o)
                    {
                        $temp[] = $o;
                    }
                }
            }
            $temp2 = array();
            foreach($temp as $o)
            {
                $temp2[] = $o;
            }
            $wynik = $temp2;

            $int_ilosc = count($wynik);
            $this->int_liczba_wierszy = $int_ilosc;
            $obj_limit = null;
            if ($int_ilosc_na_strone > 0) {
                $obj_limit = Podzial_na_podstrony::get_limit_object($int_ilosc, $int_ilosc_na_strone);
            }
            $temp = array();
            $c = 0;
            foreach($wynik as $v)
            {
                if(is_object($obj_limit))
                {
                    if($c >= $obj_limit->min && $c < ($obj_limit->min + $obj_limit->max))
                    {
                        $temp[] = $v;
                    }
                    else if($c == ($obj_limit->min + $obj_limit->max))
                    {
                        break;
                    }
                }
                else
                {
                    $temp = $wynik;
                    break;
                }
                $c++;
            }
            $paged = $temp;
        return $paged;
    }

It's one big mess, and I think that there's way to make it cleaner with SQL joins.

Thanks for help!

Update:

I want to set products in corresponding category/subcategory/subsubcategory (this is function for main categories) in specific order. It's stack-like, because every product has only its position inside specific subsubcategory. Subsubcategories have position in subcategories, subcategories in categories.

So it looks like:

category
- subcategory 2 (position 1)
-- subsubcategory 6 (position 1)
--- product 11 (position 1)
--- product 7 (position 2)
-- subsubcategory 3 (position 2) --- product 3 (position 1)
--- product 2 (position 1)

Etc. "pozycja" field is position, and number right after element is its id. So now I have to loop through elements, then set the stack and return only piece.

DB name: panelepo_sweb

Answer 1

Yes its a big mess. It would have been helpful if you'd cleaned up a bit and only shown us the parts that are causing the problem.

To summarize....

$x=SELECT x1 FROM master_table WHERE...
loop
   $y=SELECT y1 FROM next_table WHERE some_foreign_key=$x['x1']....
   loop
      $z=SELECT z1 FROM another_table WHERE some_foreign_key=$y['y1']....

Yes, this can be more efficient:

$x=SELECT master_table.x1,
      next_table.y1,
      another_table.z1
   FROM master_table, next_table, another_table
   WHERE master_table.x1=next_table.some_foreign_key
   AND next_table.y1=another_table.some_foreign_key
   ORDER BY x1, y1, z1;

Note the ordering is only required if you need to group together the records depending on their hierarchy - and you can deal with the hierarchy in a single loop by comparing the fetched value with a state variable from the previous iteration.

Although this will reduce exponentially the number of round trips to the database and the number of parses required to process the statement, it may not give a huge reduction in the overall performance.

Answer 2

I have taken a stab at rewriting the query for you. Give the follownig a try.

You didn't specify what database you use, so some syntax (e.g., TOP) may be different.

SELECT top 100 p.id 
FROM subkategorie s
INNER JOIN sub_subkategorie ss on s.id = ss.subkategorie_id
INNER JOIN produkty p on p.id=pr.produkty_id 
INNER JOIN przyporzadkowania pr ss.id = pr.sub_subkategorie_id
INNER JOIN stany_magazynowe st on st.produkty_id = p.id 
INNER JOIN gk_grupy_produkty g on p.id = g.id_produktu 
WHERE s.kategorie_id = '$id'
    AND p.widoczny='1' 
    AND g.id_grupy=$this->int_id_grupy 
    AND g.towar_widocznosc=1 
GROUP BY p.pozycja 
ORDER BY $order