Hulp bij vormen SQL query Pricewatch functionaliteit

[juggle]

Beste Tweakers,

Voor één van mijn opdrachtgevers moet ik een applicatie bouwen die een soortgelijke functie heeft als de tweakers pricewatch (Echter wel voor andere producten). De bedoeling is om aan de hand van een aantal ranged sliders (waardes van 1 tot 10) producten te selecteren die binnen een bepaalde range vallen. Mijn (vereenvoudige tabellen zien er als volgt uit:

Product tabel:

productid	name
1	product x
2	product y

Property tabel:

propertyid	title
1	property x
2	property y

Value tabel:

valueid	propertyid	productid	value
1	1	1	7
2	2	1	8
3	1	2	4
4	2	2	3

In werkelijkheid betreffen dit ongeveer 30 eigenschappen. Ik probeer via een MySQL query alle properties te controleren of zij binnen de gezette range vallen. Uiteindelijk wil ik een lijst van producten hebben waarvan alle eigenschappen binnen de gestelde waardes vallen. Dit doe ik op dit moment met een RIGHT JOIN op dezelfde tabel voor elke property. Mijn query ziet er dan als volgt uit:

code:

SELECT p.productid FROM product AS p 
RIGHT JOIN value AS v1 ON p.productid = v1.productid AND v1.propertyid = 1 AND v1.value >= 5 AND v1.value <= 10
RIGHT JOIN value AS v2 ON p.productid = v2.productid AND v2.propertyid = 2 AND v2.value >= 3 AND v2.value <= 10

Uiteraard is bovenstaand nog prima te doen met 2 properties maar met 30 zorgt dit voor een totaal niet meer te benaderen database. Ik heb het gevoel dat ik het probleem compleet verkeerd benader maar kom ook niet via de bekende weg (Google) tot andere inzichten. Wellicht dat jullie mij wat op weg kunnen helpen? Misschien iemand van de pricewatch?

[ Voor 10% gewijzigd door juggle op 09-04-2010 03:53 ]

[_js_]

Een join gebruik je niet om voorwaarden aan te geven. Met where de voorwaarden stellen zal wel snel gaan.

[Camulos]

Ik heb een beetje het gevoel dat je net begint met SQL schrijven.
Een normale query heeft namelijk altijd een SELECT + FROM (en meestal een WHERE), in de statement staan.

Heb je query iets herschreven welke tenminste zou moeten doen

code:

SELECT Product.productid
FROM Product
RIGHT JOIN Property ON Product.productid = Property.productid
RIGHT JOIN Value ON Product.productid = Value.productid
WHERE (Property.propertyid = '1' AND Value.value > '4' AND Value.value <'11') OR
(Property.propertyid = '2' AND Value.value > '2' AND Value.value <='11')

Maar ik vraag me af of dit wel zo slim is om te doen ^^, is nu niet bepaald de vorm van netheid.
Zeker als je beseft dat je voor elke property wil bekijken. Ik zou zeggen dat je beter een nested query hiervoor kunt schrijven.

[ Voor 1% gewijzigd door Camulos op 10-04-2010 20:07 . Reden: dubbele from (was nog vroeg) :) ]

[RobIII]

_js_ schreef op vrijdag 09 april 2010 @ 04:41:
Een join gebruik je niet om voorwaarden aan te geven. Met where de voorwaarden stellen zal wel snel gaan.

Euh; de dat is juist bij uitstek geschikt om je execution plan optimizer vooruit te helpen. Hoe eerder je bepaalde rijen kunt laten afvallen en je dus kunt snoeien in cartegische producten hoe beter.

[Kalentum]

Moet die RIGHT JOIN niet een INNER JOIN zijn? Je wilt sowieso toch alleen producten die aan de voorwaarden voldoen?

[_js_]

RobIII schreef op vrijdag 09 april 2010 @ 09:08:
[...]

Euh; de dat is juist bij uitstek geschikt om je execution plan optimizer vooruit te helpen. Hoe eerder je bepaalde rijen kunt laten afvallen en je dus kunt snoeien in cartegische producten hoe beter.

Misschien wil je ook even naar de query van OP kijken voor je zoiets schrijft? Het vormen van cartesische producten is juist het probleem hier. De voorwaarden in de where zetten is een van de oplossingen en maakt de query logischer door de tekst van de query meer overeen te laten komen met de vraagstelling. Inner join gebruiken zoals rutgerw voorstelt is eventueel ook een oplossing.

Daarnaast maakt het voor de execution plan optimizer niet uit of de voorwaarden in de where of in de join staat (maar er zijn wel enkele semantische verschillen).

[ Voor 10% gewijzigd door _js_ op 09-04-2010 09:17 ]

[juggle]

@Camulos

Bedankt voor je uitleg, maar 2 keer FROM gebruiken is niet helemaal de oplossing voor mij :-). De Query die jij voorstelt is niet het antwoord op de vraag. Mijn voorbeeld query werkt namelijk prima.

code:

SELECT p.productid [b]FROM[/b] product AS p 
[b]FROM[/b] Product

Het probleem is dat wanneer het aantal properties toeneemt, ik meer joins moet gaan schrijven. Dit gebeurt nu in de applicatie dynamisch.

Laten we er vanuit gaan dat ik de property tabel zelf niet nodig heb. Ik leg dus alleen een relatie tussen de product tabel en de value tabel. Ik wil dus elk product naar voren hebben waarvan alle 30 properties binnen de gestelde waardes vallen. Dus zo:

code:

SELECT p.* FROM product AS p
RIGHT JOIN value AS v1 ON 
p.productid = v1.nodeid AND v1.propertyid = 5 AND v1.value >= 0 AND v1.value <= 10
RIGHT JOIN value AS v2 ON 
p.productid = v2.nodeid AND v2.propertyid = 31 AND v2.value >= 0 AND v2.value <= 10
RIGHT JOIN value AS v3 ON 
p.productid = v3.nodeid AND v3.propertyid = 32 AND v3.value >= 0 AND v3.value <= 10
RIGHT JOIN value AS v4 ON 
p.productid = v4.nodeid AND v4.propertyid = 33 AND v4.value >= 0 AND v4.value <= 10

Dit werkt dus wel, alleen met 30 joins wordt het stroop door een rietje. Hoe kan dit anders, is een nested query niet net zo langzaam?

[Kalentum]

30 joins en dan traag? Lijkt mij dat er dan wat indexen missen.

Heb je indexen aangemaakt?

[juggle]

Ik heb indexen staan op de volgende kolommen:

table product:

PRIMARY KEY (productid)

table value

PRIMARY KEY (valueid)
KEY productid (productid)
KEY propertyid (propertyid)
KEY value (value)

[Kalentum]

En als je die langzame query uitvoert met EXPLAIN er voor, wat is dan de uitvoer?

En wat is node_id?

[NMe]

Voor zover ik weet was er in oudere versies van MySQL een reden waardoor left joins beter geoptimaliseerd werden dan right joins. Volgens mij is dat in recente versies gefixt, maar als je nog een oudere versie gebruikt zou je je query kunnen proberen te herschrijven naar left joins voor een kleine boost. Anders dan dat: 30 keer joinen op dezelfde tabel is misschien niet echt geweldig handig.

[juggle]

Eh, ok, nodeid moet productid zijn, in feite heb ik andere tabellen maar ik heb hier de versimpelde oplossing geschreven. Mijn EXPLAIN zier er zo uit:

code:

Array
(
    [0] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p1
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 22
            [Extra] => Using where
        )

    [1] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => n
            [type] => eq_ref
            [possible_keys] => PRIMARY,type
            [key] => PRIMARY
            [key_len] => 4
            [ref] => database.p1.nodeid
            [rows] => 1
            [Extra] => Using where
        )

    [2] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p3
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 22
            [Extra] => Using where
        )

    [3] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p5
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 22
            [Extra] => Using where
        )

    [4] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p7
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 22
            [Extra] => Using where
        )

    [5] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p8
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 22
            [Extra] => Using where
        )

    [6] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p2
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 23
            [Extra] => Using where
        )

    [7] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p4
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 23
            [Extra] => Using where
        )

    [8] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p6
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 23
            [Extra] => Using where
        )

    [9] => Array
        (
            [id] => 1
            [select_type] => SIMPLE
            [table] => p9
            [type] => ref
            [possible_keys] => propertyid,nodeid,value_number
            [key] => propertyid
            [key_len] => 4
            [ref] => const
            [rows] => 23
            [Extra] => Using where
        )

)

Query:

SQL:

EXPLAIN SELECT n.* FROM node AS n 
RIGHT JOIN module_review_property AS p1 ON n.nodeid = p1.nodeid AND p1.propertyid = 5 AND p1.value_number >= 0 AND p1.value_number <= 10 
RIGHT JOIN module_review_property AS p2 ON n.nodeid = p2.nodeid AND p2.propertyid = 31 AND p2.value_number >= 0 AND p2.value_number <= 10 
RIGHT JOIN module_review_property AS p3 ON n.nodeid = p3.nodeid AND p3.propertyid = 32 AND p3.value_number >= 0 AND p3.value_number <= 10 
RIGHT JOIN module_review_property AS p4 ON n.nodeid = p4.nodeid AND p4.propertyid = 33 AND p4.value_number >= 0 AND p4.value_number <= 10 
RIGHT JOIN module_review_property AS p5 ON n.nodeid = p5.nodeid AND p5.propertyid = 34 AND p5.value_number >= 0 AND p5.value_number <= 10 
RIGHT JOIN module_review_property AS p6 ON n.nodeid = p6.nodeid AND p6.propertyid = 35 AND p6.value_number >= 0 AND p6.value_number <= 10 
RIGHT JOIN module_review_property AS p7 ON n.nodeid = p7.nodeid AND p7.propertyid = 36 AND p7.value_number >= 0 AND p7.value_number <= 10 
RIGHT JOIN module_review_property AS p8 ON n.nodeid = p8.nodeid AND p8.propertyid = 37 AND p8.value_number >= 0 AND p8.value_number <= 10 
RIGHT JOIN module_review_property AS p9 ON n.nodeid = p9.nodeid AND p9.propertyid = 38 AND p9.value_number >= 0 AND p9.value_number <= 10 
WHERE n.type = 'review_item'

[ Voor 0% gewijzigd door NMe op 09-04-2010 12:51 . Reden: Code-tags ]

[juggle]

@NMe

haha, nee heh, zo'n vermoeden had ik al. Maar goed, hoe zou ik het anders moeten doen? Hoe gebeurt dit bijvoorbeeld in de pricewatch bij tweakers?

[Kalentum]

NMe schreef op vrijdag 09 april 2010 @ 12:47:
Anders dan dat: 30 keer joinen op dezelfde tabel is misschien niet echt geweldig handig.

Met zo'n datamodel ontkom je daar niet aan. En op zich maakt het niet uit: of het nu dertig verschillende tabellen zijn of dertig keer dezelfde tabel.

juggle schreef op vrijdag 09 april 2010 @ 12:49:
Eh, ok, nodeid moet productid zijn, in feite heb ik andere tabellen maar ik heb hier de versimpelde oplossing geschreven. Mijn EXPLAIN zier er zo uit:
[...]

Die explain met 'const' overal is best efficient. Wat gaat er dan mis? Hoe lang doet deze query erover? En die met dertig joins?

[juggle]

Het gekke is dat zodra ik 1 join eraan toevoeg de database niet meer reageert todat ik het process stopzet.

[GlowMouse]

Probeer de query eens te draaien met een WHERE n.nodeid IN (SELECT nodeid FROM property WHERE ...) AND n.nodeid IN (...)

Als dat te traag wordt kun je dit nog proberen
SELECT DISTINCT nodeid FROM (
(SELECT property WHERE ...)
UNION
(SELECT property WHERE ...)
UNION
(SELECT property WHERE ...)
)
en dan met een tweede query de nodes ophalen. Zorg wel voor één index op (propertyid,value).

T.Net heeft niet voor niets middleware ontwikkeld trouwens, want dit zijn gewoon vervelende queries

[ Voor 68% gewijzigd door GlowMouse op 09-04-2010 13:27 ]

[RobIII]

_js_ schreef op vrijdag 09 april 2010 @ 09:15:
Misschien wil je ook even naar de query van OP kijken voor je zoiets schrijft?

Euh; jij maakte een algemene opmerking; ik niet Ik had 't dan ook niet over de query van TS maar reageerde op jouw algemene opmerking.

_js_ schreef op vrijdag 09 april 2010 @ 09:15:
Daarnaast maakt het voor de execution plan optimizer niet uit of de voorwaarden in de where of in de join staat (maar er zijn wel enkele semantische verschillen).

Ik kan de bron niet meer vinden zo snel, maar MySQL heeft (of had in het verleden) daar wel degelijk voordeel bij (gehad). Ik weet niet of dat nog zo is.
Hoe dan ook; ik doe het zelf altijd op de joins. Persoonlijk vind ik dat ook leesbaarder.

[ Voor 11% gewijzigd door RobIII op 09-04-2010 13:35 ]

[juggle]

Nou, het is niet te geloven, ik heb de indexes verwijderd en opnieuw aangemaakt en nu draait de query als een tierelier. Raar dat MySql....

In ieder geval, even voor de duidelijkheid, voorwaarden zet ik ALTIJD in de joins waar mogelijk aangezien dit de query een stuk leesbaarder maakt. Volgens mij maakt het voor de performance niet uit.

[ Voor 42% gewijzigd door juggle op 09-04-2010 13:51 ]