[PHP/MySQL] Filter systeem, prestatie en query

[royduin]

Beste tweakers,

n.a.v. mijn vorige topic: [MySQL] Selecteer waar overal aanwezig heb ik toch weer wat vragen. Wat daar besproken is gaat allemaal uit de kunst. Nu het vervolg, de juiste producten aan de hand van de gekozen filters echo'en.

Om te beginnen zal ik wat code "neer gooien":

PHP:

<?php
            //Algemene filter query opbouwen
            if($_GET['merk']){
                $selecteren .= " AND fabrikant='".secure($_GET['merk'])."'";
            }

//Timers
$timer_start = microtime(true);
echo "Start: ".microtime(true)."<br />";
echo "0<br />";

            //Producten weergeven
            $query5 = mysql_query("SELECT id,fabrikant,naam FROM producten_new WHERE icecat_cat='".secure_in($obj->icecat)."'".$selecteren."");
            echo '<div class="aanbieding"><p>';
            while($row5 = mysql_fetch_assoc($query5)){

                //TODO!!!
                //Als er geen specs filter is gekozen gewoon alle producten weergeven

                $product_niet_aanwezig = FALSE;
                
                //Specs filter query opbouwen
                foreach($_GET as $key=>$value){
                    if($product_niet_aanwezig == FALSE){
                        if($key != "p" AND $key != "id" AND $key != "merk" AND $key != "submit" AND $value != "0"){
                            
                            $query12 = mysql_query("SELECT COUNT(id) as aantal FROM producten_specs WHERE name='".secure_in(str_replace("_", " ", $key))."' AND value='".secure_in($value)."' AND product_id='".secure_in($row5['id'])."'");
                            $data12 = mysql_fetch_assoc($query12);

//Teller met dubbel L
$telller++;
                            
                            if($data12['aantal']){
                                $product_aanwezig = TRUE;
                            } else {
                                $product_niet_aanwezig = TRUE;
                                $product_aanwezig = FALSE;
                            }

                        }
                    }
                }

                
                if($product_aanwezig){
                    echo '<a href="index.php?p=product&id='.$row5['id'].'">'.$row5['fabrikant'].' - '.$row5['naam'].'</a><br />';

//Timers
echo microtime(true) - $timer_start.'<br />';

                    $product_aanwezig = FALSE;
                    $producten_weergeven = TRUE;
                }
                
            }
            
            if($producten_weergeven == FALSE){
                echo 'Geen producten gevonden.';
            }

//Timers
$timer_einde = microtime(true) - $timer_start;
echo "Einde: ".$timer_einde.'<br />';       
echo 'query12 uitgevoerd: '.$telller;
?>

Voorbeeld van de URL (waarbij enkele filters gekozen zijn):

code:

index.php
?p=categorie
&id=5
&merk=Asus
&Beeldschermdiagonaal=0
&Resolutie=0
&Inclusief+besturingssysteem=0
&Ingebouwde+camera=0
&Chipset=0
&Processor+model=0
&Processor-kloksnelheid=2400+MHz
&Processorfamilie=0
&Harde+schijf-interface=0
&Rotatiesnelheid=0
&Totale+opslagcapaciteit=500+GB
&Aansluiting+voor+netstroomadapter=0
&VGA+%28D-Sub%29poort%28en%29=0
&Grafische+adapter=0
*knip (anders wordt het overdreven lang)*
&submit=Filter+toepassen

Database
De database daar hebben we producten_new (3500 items) en producten_specs (130.000 items).

Producten_new met product algemene informatie:
id, leverancier, prijs, productgroep, omschrijving, enz.

Producten_specs met alle specificaties:
id, product_id, cat, name, value


Het probleem / wat moet er gebeuren.

1) Wanneer dit uitgevoerd wordt duurt het gigantisch lang (12 seconden wanneer er één specificatie filter gekozen wordt) voordat er echt wat op het scherm verschijnt. Wilde wat proberen met eerst alle gets op een rijtje zetten om dit later te gebruiken. Om dit juist toe te passen ben ik niet helemaal uit gekomen. Naast dat denk ik achteraf dat dit niet erg in prestatie zal schelen:

PHP:

<?php
            //Specs op een rijtje zetten
            foreach($_GET as $key=>$value){
                if($key != "p" AND $key != "id" AND $key != "merk" AND $key != "submit" AND $value != "0"){
                    $specs_gets .= $key.",".$value.";";
                }
            }
            //Laatste ; weg halen
            $specs_gets = substr($specs_gets, 0, -1);
?>

2) Wanneer ik bijv. pagina nummering wil toevoegen moet alles in één query (momenteel 2). Of ik moet er een query bij maken. Producten (id's) met algemene filters selecteren, producten (id's) met specificatie filters selecteren en vervolgens een query maken waar deze id's geselecteerd worden en dan pas echo'en. Waarbij we bij de laatste query dus "limit" kunnen toevoegen. Wat is handig?

3) Als er geen specificatie filters geselecteerd zijn maar wel een merk werkt dit momenteel niet. Naast dat als er helemaal geen filters gekozen zijn hebben we ook 0 resultaat. Ook hier heb ik tot op heden nog geen oplossing voor.

Ik hoop dat ik het duidelijk heb uitgelegd en iemand wat advies kan geven.
Wordt mij inmiddels teveel "gerommel" eerst maar eens horen wat voor manier het snel/goed werkt.

Alvast bedankt!

Roy

[ Voor 24% gewijzigd door royduin op 16-03-2011 22:54 ]

[McKaamos]

1) Wat tekst op een rij zetten kost je de kop niet.
2) Oftewel: alles in 1 keer laden of eerst pollen hoeveel results je krijgt om op basis daarvan limit te gebeuiken. Eerste load zal allicht traag zijn maar opvolgende zijn snel.
Je kan ook in het wilde weg limits gaan stellen en in je code blijven tellen tot je een resultset terugkrijgt die niet zo groot is als je limit. Je kan dan alleen geen nummers gebruiken, slechts een "next" knop.
Andere optie is alles in 1 keer in een session variable zetten zodat de query niet opnieuw uitgevoerd hoeft te worden.
3) Blijkbaar geeft je code wel output ondanks dat er geen opties aangegeven zijn. Misschien een extra check inbouwen?

Verder, als je bezorgd bent om de tijd die het kost, gooi dan eens wat timers in je code en echo de results onderaan de pagina. Kan je precies uitvogelen wat zoveel tijd kost en waar je blijkbaar moet optimaliseren.
Sowieso hebben we zonder een voorbeeld van een rij uit je tabel weinig aanknopingspunten.

PHP:

<?php
$timer_start = microtime();

// Wat code

$timer_check1 = microtime() - $timer_start; #eerste checkpoint

// Meer code

$timer_check2 = microtime() - $timer_start; #tweede checkpoint

// Nog meer code

$timer_check3 = microtime() - $timer_start; #derde checkpoint

echo $timer_check1;
//etc etc
?>

[ Voor 14% gewijzigd door McKaamos op 15-03-2011 19:51 ]

[MueR]

Gebruik dan wel get_as_float, dat scheelt namelijk strings parsen.

PHP:

$start = microtime(true);
$one = microtime(true) - $start;
// etc
// Meer dan honderd duizendsten zijn vaak niet zo boeiend,
// als je al niet met duizendsten genoeg info krijgt..
echo round($start, 5).'<br />';
echo round($one, 5).'<br />';

[ Voor 6% gewijzigd door MueR op 15-03-2011 20:27 ]

[royduin]

Heb zo her en der wat "timers" geplaatst en het resultaat bekeken maar wordt er eerlijk gezegd niet echt wijs van. Met onderop het aantal keren dat query12 uitgevoerd wordt.

Even een linkje erbij, spreekt wat meer: *knip*
Wat vinden jullie van de snelheid? Zit hier af en toe zo 10 seconden te wachten voor resultaat.

[ Voor 16% gewijzigd door royduin op 19-03-2011 13:15 ]

[Creepy]

De tijden die er nu worden getoond zeggen ons echt helemaal niks omdat niet duideijk is waar die tijden bij horen. Dus controleer nu zelf eerst eens waar de tijd in gaat zitten en probeer dat aan te pakken. Zolang je niet weet wat er nu traag is. kan je het ook niet sneller maken

[McKaamos]

royduin schreef op dinsdag 15 maart 2011 @ 21:08:
Heb zo her en der wat "timers" geplaatst en het resultaat bekeken maar wordt er eerlijk gezegd niet echt wijs van. Met onderop het aantal keren dat query12 uitgevoerd wordt.

Even een linkje erbij, spreekt wat meer: http://tinyurl.com/4hvgcs8
Wat vinden jullie van de snelheid? Zit hier af en toe zo 10 seconden te wachten voor resultaat.

Wat Creepy al zegt.
Het is huidige tijd - start tijd, dus de tijden zijn checkpoints vanaf het punt dat het script start.
Wanneer land hij bij lokatie X.
Uiteraard dien jij die lokaties zorgvuldig te kiezen. Bijvoorbeeld na het verwerken van de input (zoek filter), na het verwerken van de query en het einde van het script.
Vertel ons dan ook even waar ze staan zodat wij hier er eventueel ook nog wat nuttigs over kunnen zeggen.

[royduin]

Mijn begin post aangepast met daarin de timers (lijkt me prima zo?). Gaat het allemaal wel goed omdat er een while en foreach gebruikt worden? Ook even wat info m.b.t. de database in de begin post geplaatst.

Zojuist een filter toegepast, zit 7 seconden te wachten. Resultaat onderop de pagina?!?:
1300225136.56
11.37304
11.55915
11.74535
11.74538
11.74539
query12 uitgevoerd: 56

[McKaamos]

Nou, dan is dat duidelijk, het probleem huist tussen de start en de eerste timer.
Ergens met die eerste query gaat het dus verkeerd.

Eerste wat me daar opvalt zijn wat variabelen in de query waarvan de herkomst mij onduidelijk is.
Ook zie ik een ";" staan zonder dat daar een " voor staat om de ) af te sluiten.

edit:
Oh, en je eerste timerlokatie is nogal ongelukkig gekozen. Deze wordt steeds overschreven doordat hij binnen de loop zit.
Trek de loop en de query eens uit elkaar en parkeer daar de timer eens tussen.

En als je dan toch in loops wil meten, echo ze dan voor het einde van de loop of zorg dat ze opgeslagen worden om ze later in een tabel te zetten zodat je precies elke loop door kan neuzen

[ Voor 40% gewijzigd door McKaamos op 15-03-2011 23:05 ]

[Creepy]

Om maar even iets minder subtiel te zijn dan net: waar denk je nu zef dat het probleem zit qua traagheid en hoe ga je dat nu aanpakken? Je doet nu niks meer dan je code dumpen en wachten totdat wij aangeven hoe en wat, en dat is nu net niet de bedoeling. PRG draait om zeg programmeren, niet om anderen dat voor je te laten doen

[royduin]

Ik verwacht niet dat alles voorgekauwd wordt, maar twijfel of ik het wel goed doe. Vandaar dat ik de hele lap code neer "gooi" en hoop dat iemand zegt, waarom doe je het in godsnaam zo. Heb zo'n reactie niet gehad wat denk ik inhoud dat ik er wel goed over nagedacht en uitgewerkt heb.

Vanavond ga ik weer verder "rommelen" en hoop er dan achter te komen waardoor het zo lang duurt.
Denk zelf dat het niet aan het script ligt maar aan de hoeveelheid informatie in de database...?

Wat puntje 2 betreft, een 3e query erbij is het meest makkelijke denk ik? Deze query's samenvoegen gaat niet / lastig.

Puntje 3: Als ik dan toch bezig was met vragen stellen heb ik deze er ook maar bij gezet. Hier ga ik wel uit komen.

[ Voor 7% gewijzigd door royduin op 16-03-2011 11:54 ]

[Jeffroiscool]

royduin schreef op woensdag 16 maart 2011 @ 11:45:
Ik verwacht niet dat alles voorgekauwd wordt, maar twijfel of ik het wel goed doe. Vandaar dat ik de hele lap code neer "gooi" en hoop dat iemand zegt, waarom doe je het in godsnaam zo. Heb zo'n reactie niet gehad wat denk ik inhoud dat ik er wel goed over nagedacht en uitgewerkt heb.

Vanavond ga ik weer verder "rommelen" en hoop er dan achter te komen waardoor het zo lang duurt.
Denk zelf dat het niet aan het script ligt maar aan de hoeveelheid informatie in de database...?

Wat puntje 2 betreft, een 3e query erbij is het meest makkelijke denk ik? Deze query's samenvoegen gaat niet / lastig.

Puntje 3: Als ik dan toch bezig was met vragen stellen heb ik deze er ook maar bij gezet. Hier ga ik wel uit komen.

Daar hebben we codereview.stackexchange.com voor, deze gasten zijn daar ontzettend goed in

[Voutloos]

Verbeter iig eerst de locatie van $timer1 zodat je wel enkel de duur van 'query5' meet, en bekijk de individuele tijden van alle 'query12' queries.

Overigens lijkt het mij ook wel mogelijk om het aantal queries te reduceren.

[royduin]

Vanavond een nieuw bed gehaald, moet ook gebeuren
Alweer eventjes terug hierna direct weer aan de gang.

Waar zit het probleem? In query12 (query weg gehaald en onderop $product_aanwezig = TRUE; gezet, gaat snel dan). Deze wordt velen malen uitgevoerd, in mijn laatst uitgevoerde filter actie 91 maal.
Wanneer deze query 10 maal wordt uitgevoerd is dit redelijk te doen qua tijd. Helaas komt dit zelden voor.

Aan de query zelf kan er weinig getweakt worden? Denk dat dit de snelste methode is om op te vragen of iets aanwezig is.

Hoe ga ik dit probleem dan toch oplossen?
- Hoor links en rechts het een en ander over indexen in de database (aangezien er behoorlijk wat in staat)?
- Server instelling om met mysql snellere of meerdere verbindingen tegelijk te maken?
- Denk ik totaal verkeerd en kan ik dit alles makkelijker met minder query's uitvoeren?

[moozzuzz]

Kan je nog es uitleggen wat je code exact doet? Of zou moeten doen...

1. Q5 selecteer producten van (al dan niet op basis van leverancier, categorie, ...)
aantal rows: onbekend (voor debugging mss wel interessant te melden)

2. Q12 selecteer het aantal productspecs met een bepaalde naam, value, productid; gelooped per product
aantal rows: 0 of 1

Dus als q12 56x is uitgevoerd, heb je dit 56 queries teveel. Gewoon joinen die boel.

Ik ga er vanuit dat je een aantal debug-variablen hebt geintroduceerd; nu kan het altijd helpen deze logische namen te geven: "$product_niet_aanwezig" heeft vermoedelijk iets te zien met specs.

[royduin]

In de database heb ik alle product informatie:

producten_new (3500 items)
id, leverancier, prijs, icecat_cat, omschrijving, fabrikant, enz.
icecat_cat is de productgroep

producten_specs (130.000 items)
id, product_id, cat, name, value

Producten_new spreekt voor zich denk ik, hier staat alle "algemene" informatie over een product.
In producten_specs staan de specificaties per product. Voorbeeld:
1, 1234, Processor, Kloksnelheid, 2400 Mhz
2, 1234, Hardeschijf, Capaciteit, 500 GB

Nu ben ik bezig met een product filter zodat er op specificaties gefilterd kan worden. Er wordt dus bijv. gekozen voor een productgroep (icecat_cat) bijv. laptops en gekozen voor het merk Asus. Vervolgens wil iemand een product hebben met een kloksnelheid van 2400 Mhz. Dit wordt gekozen en de filter wordt uitgevoerd.

Nu worden eerst de producten geselecteerd (Q5), waar de categorie en het merk overeen komen.
Vervolgens worden bij al deze geselecteerde producten gekeken (Q12) in producten_specs de cat, name en value (Processor, Kloksnelheid, 2400 Mhz) aanwezig zijn.
Er moeten ook meerdere filters gekozen kunnen worden (nu ook mogelijk), bijv. kloksnelheid en hardeschijf capaciteit.

Dit werkt nu top maar gigantisch traag door het loopen en veel uitvoeren van Q12.

Nogmaals het linkje erbij zodat het duidelijker wordt wat er gebeurd: *knip*

Het joinen van query's is niet mijn sterkste punt en lijkt me vrij lastig met wat er hier moet gebeuren? Dan mijn 2e en 3e vraag uit de start post niet te vergeten.. Zijn deze 2 query's te joinen met al deze "eisen"?

[ Voor 3% gewijzigd door royduin op 19-03-2011 13:16 ]

[ACM]

royduin schreef op donderdag 17 maart 2011 @ 12:05:
Het joinen van query's is niet mijn sterkste punt en lijkt me vrij lastig met wat er hier moet gebeuren? Dan mijn 2e en 3e vraag uit de start post niet te vergeten.. Zijn deze 2 query's te joinen met al deze "eisen"?

Waarom zouden het er 2 zijn?

Als je 3 filters bovenop je merk, dan heb je toch 4 queries nu (nouja, 1 query plus voor elk gevonden product nog 3)? Zorg er dan ook voor dat je code effectief die 4 queries joined. Als je handig gebruik maakt van table aliassen, dan kan je prima dezelfde tabel (jouw specs bijvoorbeeld) onder een andere en met een ander doel (verschillende spec-filters bijv) in dezelfde query hergebruiken. Zo kan je dus een vrij complexe query bouwen die 1x je producten-tabel en dan voor elk spec-filter via een join een filter erbij krijgt.

Als je dan de meest logische index op je specificaties-tabel al hebt liggen, dan zou dat vrij rap moeten gaan.

[royduin]

Ben alweer een stuk dichter bij
Geheel anders gedaan, heb hiermee bijna hetzelfde resultaat. Dat ook een stuk sneller. Het enige wat nog mist is de product_id IN ('".secure_in($query5_ids)."') in de query (12). Zie de quote erboven. Ook dan zou de quote boven $uiteindelijk_selecteren gebruikt moeten worden.
Ik hoop op een oplossing. Eventuele aan- of opmerkingen op dit script zijn ook welkom!

PHP:

<?php
            //Producten selecteren uit categorie en merk
            $query5 = mysql_query("SELECT id FROM producten_new WHERE icecat_cat='".secure_in($obj->icecat)."'".$selecteren."");
            while($row5 = mysql_fetch_assoc($query5)){
                $query5_ids .= $row5['id'].", ";
            }
            //Laatste , weg halen
            $query5_ids = substr($query5_ids, 0, -2);
            
            //Gets op een rijte zetten
            foreach($_GET as $key=>$value){
                if($key != "p" AND $key != "id" AND $key != "merk" AND $key != "submit" AND $value != "0"){
                    $specs_gets .= $key.",".$value.";";
                    $aantal_gets++;
                    $uiteindelijk_selecteren .= "name='".secure_in(str_replace("_", " ", $key))."' AND value='".secure_in($value)."' OR ";
                }
            }
            //Laatste ; weg halen
            $specs_gets = substr($specs_gets, 0, -1);
            
            
            //$uiteindelijk_selecteren = " AND ".substr($uiteindelijk_selecteren, 0, -4);
            $uiteindelijk_selecteren = substr($uiteindelijk_selecteren, 0, -4);
            
            
            //Overeenkomend met de get's selecteren
            //$query12 = mysql_query("SELECT product_id FROM producten_specs WHERE product_id IN ('".secure_in($query5_ids)."') ".$uiteindelijk_selecteren." ORDER BY product_id ASC");
            $query12 = mysql_query("SELECT product_id FROM producten_specs WHERE ".$uiteindelijk_selecteren." ORDER BY product_id ASC");
            while($row12 = mysql_fetch_assoc($query12)){
                
                if($product_onthouden == $row12['product_id']){
                    $product_onthouden_aantal++;
                } else {
                    $product_onthouden_aantal = FALSE;
                    $product_onthouden = $row12['product_id'];
                    $product_onthouden_aantal = 1;
                }
                
                if($product_onthouden_aantal == $aantal_gets){
                
                    $query12_ids_ok .= $row12['product_id'].", ";
                    $aantal_producten++;
                }
                
            }
            //Laatste , weg halen
            $query12_ids_ok = substr($query12_ids_ok, 0, -2);
?>

EDIT:
Heb hem

PHP:

$query12 = mysql_query("SELECT product_id FROM producten_specs WHERE product_id IN (".secure_in($query5_ids).") AND ".$uiteindelijk_selecteren." ORDER BY product_id ASC");

Nu nog wat verder uitwerken, lijkt erop dat het verder moet lukken. Ik laat het nog horen!

[ Voor 6% gewijzigd door royduin op 18-03-2011 23:25 ]

[4VAlien]

Is de kern van je probleem: "Ik moet producten ophalen die matchen met alle filters die in de query staan" ? Het lijkt me wel maar het is altijd handig als dit even duidelijk is.

ik denk dat het meest optimale resultaat gehaald wordt met een enkele query met daar in de productNew table en N joins voor N geselecteerde specs met de spec tabel.

De vraag is dus: weet je wat een join is, en kun je het verschil tussen een join en een left join uitleggen? Zo niet dan heb je een probleem in deze situatie omdat je verplicht bent om alle producten en specs op te halen en apart in je code te matchen (waarbij je waarschijnlijk producten * specs = heel veel iteraties nodig hebt).

Ik zie dat je een extra categorie hebt waardoor je maar een deel van de producten en specs ophaalt, maar hou er rekening mee dat deze code langzamer wordt naarmate je meet producten in een groep hebt, of meer specificaties toevoegt aan bestaande producten. Je maakt nog steeds producten * specs iteraties waar je ook een query kan hebben waarbij de complexiteit voornamelijk bepaalt wordt door het aantal specs.