[MySQL] Zoekresultaten beperken d.m.v. efficiënte query

[Verwijderd]

Ik ben bezig een site te ontwikkelen (m.b.v. PHP & MySQL) die functionaliteit bevat die enigszins overeen komt met die van BizRate. Bij BizRate is het namelijk mogelijk om zoekopdrachten te verfijnen door items in kolommen bovenaan de resultatenpagina's te klikken. Als je bijvoorbeeld in de categorie digitale camera's zit, is het mogelijk om het aantal resultaten te verkleinen door in de kolom Resolution te kiezen voor camera's met 5 megapixels of meer. De zoekopdracht wordt dan uitgebreidt en de kolom Resolution verdwijnt van de resultatenpagina. Nou is het ook zo dat per verfijning genoteerd staat hoeveel resultaten er nog overblijven wanneer deze verfijning gekozen wordt (door middel van een getalletje tussen haakjes achter de verfijning).

Nou heb ik dit gedrag nagebootst door per verfijning een query uit te voeren. Als een verfijning nul resultaten oplevert wordt deze niet getoond. Dit is echter ontzettend traag, doordat er heel veel queries uitgevoerd moeten worden wanneer de pagina opgebouwd wordt (en de tabel met producten heeft enkele miljoenen records).

Weet iemand hoe dit efficiënt aangepakt moet worden (oftewel: hoe beperk ik het aantal benodigde queries)? Het in één keer voor alle verfijningen bepalen hoeveel resultaten ze opleveren (d.m.v. een of andere mega-query) lijkt me niet te doen...but correct me if I'm wrong!

[Nick_S]

Laat eerst eens een database model en wat SQL queries, die je uitvoerd, zien. Aan de hand daarvan kunnen we misschien wat tips geven, maar hier kan ik in ieder geval weinig mee.

[Verwijderd]

waarom niet de queries aan elkaar te hangen?
zoals:

PHP:

query1 ="where pixel = 5"
query0 = ''where camera like "digital"

if exist query1
     query = "AND ".query1
if exist query0
     query = "AND ".query0

dan krijg je 1 grote query

[ Voor 14% gewijzigd door Verwijderd op 17-02-2005 14:31 ]

[dajappie]

Volgens mij wordt deze informatie bij Bizrate ook maar 1 keer per dag gegenereerd en dan als statische pagina's neergezet. Aangenomen dat de informatie en de categorieen bij jou ook niet elk moment veranderen is dit een nette oplossing, belasting op je database blijft beperkt.

[Verwijderd]

Nick_S schreef op donderdag 17 februari 2005 @ 14:29:
Laat eerst eens een database model en wat SQL queries, die je uitvoerd, zien. Aan de hand daarvan kunnen we misschien wat tips geven, maar hier kan ik in ieder geval weinig mee.

Het uit mijn databasemodel en code filteren wat interessant is, is erg lastig. Eigenlijk hoef ik geen kant-en-klare SQL queries te hebben, maar ben ik meer benieuwd hoe ze bij BizRate het aangepakt hebben.

Verwijderd schreef op donderdag 17 februari 2005 @ 14:30:
waarom niet de queries aan elkaar te hangen?
zoals:
...
dan krijg je 1 grote query

Volgens mij is het probleem niet helemaal duidelijk. Het gaat erom om niet alleen de resultaten te beperken, maar ook per "verfijnmogelijkheid" te berekenen hoeveel resultaten er over blijven wanneer deze verfijning gekozen wordt.

dajappie schreef op donderdag 17 februari 2005 @ 14:32:
Volgens mij wordt deze informatie bij Bizrate ook maar 1 keer per dag gegenereerd en dan als statische pagina's neergezet.

Dat lijkt me sterk, aangezien er ontzettend veel (exponentieel) mogelijkheden zijn. Stel dat er tien kolommen zijn met ieder tien verfijningen, dan moeten er per categorie 10 ^ 10 aantal queries uitgevoerd worden om de boel te berekenen...

[BasieP]

misschien gebruiken ze wel arrays of whatever php heeft om grote hoeveelheden data op te slaan. Dan kan het zijn dat ze gewoon 1x in de database duiken om een hele berg info te zoeken, waarna ze gewoon met arrays de verfijning doen.

maareuh als je het zo graag wilt weten, waarom kijk je dan niet in de code van BizRate?

[dajappie]

Misschien hebben ze wel een aparte tabel waar ze per categorie het aantal onderliggende elementen bijhouden. Als een produkt wordt toegevoegd of verwijderd passen ze simpelweg voor die categorieen een tellertje aan. Stel bij een nieuwe Canon EOS-300D pas je eenmalig de tellers aan voor Brand, Price Range, Digital Zoom etc. Vervolgens hoef voor de pagina met digitale camera's alleen de matchende subcategorieen uit te lezen voor die categorie. Da's slechts 1 query!

[Verwijderd]

Jullie stellen eigenlijk hetzelfde voor, dajappie & BasieP, maar ik ben bang dat BizRate niet zo werkt: je kunt namelijk ook een eigen zoekopdracht uitvoeren waarna de tellers automatisch worden bijgewerkt. Dat zou op de door jullie voorgestelde manier niet mogelijk zijn, toch?

BasieP schreef op donderdag 17 februari 2005 @ 15:06:
...
maareuh als je het zo graag wilt weten, waarom kijk je dan niet in de code van BizRate?

Als het zo eenvoudig was, had ik dat allang gedaan! Je kunt helaas met "view source" niet meer zien dan de gegenereerde HTML...

[dajappie]

Verwijderd schreef op donderdag 17 februari 2005 @ 15:27:
Jullie stellen eigenlijk hetzelfde voor, dajappie & BasieP, maar ik ben bang dat BizRate niet zo werkt: je kunt namelijk ook een eigen zoekopdracht uitvoeren waarna de tellers automatisch worden bijgewerkt. Dat zou op de door jullie voorgestelde manier niet mogelijk zijn, toch?

Nope dat klopt. Als een dergelijke constructie incl. het zoeken voor jou ook noodzakelijk is zal je idd (bijna) alles dynamisch moeten doen. Ik zou waarschijnlijk een dbstructuur maken waarin je binnen een huidige categorie snel alle subcategorieen kan vinden (boompje met parentID's ofzo). Vervolgens vraag je voor al die subcategorieen tegelijk in één query alle produktID's op waarmee je via GROUP BY en COUNT in SQL direct de tellingen uitvoert. Via een tweede query vraag je zoals BizRate de volledige produktinformatie op voor een gedetailleerd overzicht binnen de huidige gekozen categorie.

[Verwijderd]

Bedankt voor je reactie, dajappie! Het ophalen van de subcategoriën is niet nodig, aangezien de zoekactie slechts binnen de huidige categorie hoeft plaats te vinden. Het probleem gaat die GROUP BY/COUNT query worden, aangezien de details van een product waarop verfijnd kan worden (zoals bijvoorbeeld het merk) niet in de producttabel staan. De tabelstructuur is namelijk de volgende (let niet op mijn ASCII-art kunsten... ):

      +----- tblCategoriesPerProduct ----- tblCategories
      |
tblProducts
      |
      +----- tblDetailsPerProduct ----- tblDetails

In de tussentabel tblDetailsPerProduct staat per product-detail combinatie de waarde opgeslagen, dus bijvoorbeeld Philips wanneer het detail merk is.

Dit is de huidige query waarmee ik voor een bepaald detail (in combinatie met de huidige zoekactie) bepaal hoeveel resultaten dit op zou leveren:

SELECT COUNT(*)
FROM tblProducts, tblCategoriesPerProduct, tblDetailsPerProduct
WHERE MATCH(title,description) AGAINST ('huidige zoekactie' IN BOOLEAN MODE)
AND tblCategoriesPerProduct.product_id = tblProducts.product_id
AND tblDetailsPerProduct.product_id = tblProducts.product_id
AND tblCategoriesPerProduct.category_id = id van huidige categorie
AND tblDetailsPerProduct.detailtype_id = id van detail

Na uren prutsen lukt het me nog steeds niet om deze query zo uit te breiden dat deze de COUNT laat zien voor alle mogelijke details. Iemand een idee?

[NoFearWizz]

je kan controleren om meerdere eigenschappen van een result uit je tabel door intersect te gebruiken

code:

SELECT * 
FROM tbl_products
WHERE type = "digital"

INTERSECT

SELECT *
FROM tbl_products
WHERE mp = "5";

hierdoor krijg je dus alle camera's die zowel digital zijn en 5 megapixels hebben
dit kan je heel makkelijk samenvoegen door een formulier te maken.
en dan bijv zo optelossen:

code:

<form action="uitwerking.php" method="post">
<input type="text" name="searchstring"><br>
<input type="checkbox" name="type" value="digital">Digital<br>
MP: <input type="text" name="mp"><br>
<input type="submit" name="submit" value="vraag op!">
</form>

PHP:

//uitwerking.php:

$query="SELECT * 
FROM tbl_products"; // De gewone gegevens ophalen

if ($_POST["type"] != ""){
$query .= "WHERE type = 'digital' "; // De eigenschap digitaal toevoegen
}

if ($_POST["type"] != "" && $_POST["mp"] != ""){
$query .= "INTERSECT"; // De koppeling/vergelijking maken
}

if ($_POST["mp"] != ""){
$query .= "SELECT * 
FROM tbl_products
WHERE mp = '5' "; // De laatste eigenschap toevoegen
}
$query .= ";"; // Query afsluiten

hoop dat je hier wat aan hebt
Deze heb ik ff snel gemaakt als je wat langer er voor gaat zitten kan je het veel beter in elkaar steken.

Ik denk zoiezo dat BizRate een serverside script gebruikt anders als PHP of ASP, ik denk eerlijk gezegt aan Perl of CGI. dat is zoiezo al sneller als php.
en waarschijnlijk een msacces database of postgre of iets anders als mysql (mss wel Oracle).
Dan draait het veel sneller als een server thuis met php/asp (omdat mysql zoiezo al langzaam is)

[ Voor 185% gewijzigd door NoFearWizz op 17-02-2005 23:18 . Reden: wat wijzigingen aangebracht ;) ]

[dajappie]

NoFearWizz schreef op donderdag 17 februari 2005 @ 23:05:
[...]
Ik denk zoiezo dat BizRate een serverside script gebruikt anders als PHP of ASP, ik denk eerlijk gezegt aan Perl of CGI. dat is zoiezo al sneller als php.
en waarschijnlijk een msacces database of postgre of iets anders als mysql (mss wel Oracle).
Dan draait het veel sneller als een server thuis met php/asp (omdat mysql zoiezo al langzaam is)

offtopic:
Scripttaal is hier sowieso grotendeels ondergeschikt aan de database gezien het mogelijke gewicht van de queries, daarbij is CGI geen taal maar slechts een interface naar bv. Perl of C-programma's vanuit je webserver. Verder is Access minstens net zo ongeschikt als MySQL voor schaalbare oplossingen voor grote e-commerce sites, denk dan eerder aan Oracle, SQL-server of DB2-achtige enterprise databases.

[Janoz]

Alle gegevens bovenin haal je toch al op in je query? Bij het fetchen van de resultaten kun je rustig enkele tellertjes bijhouden van bepaalde filters.