Hallo,
ich schicke einen Query auf eine ganze Menge Datensätze. Und da ich das ganze auch noch nach einem aggregierten Wert sortiere, wird mir das leider zu langsam. Ich suche nun nach einer Möglichkeit, meine Abfrage weiter zu optimieren, oder eine Beratung, welchen Serverteil ich aufrüsten muss, damit das Teil flutscht.
Ich beschreib euch das ganze mal:
Ich habe 3 Tabellen:
bauteile (ca. 5.000 Datensätze)
[TABLE=“class: outer_border”]
[TR]
[TD]id[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Transistor[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Klebeband[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Handtuch[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Schrauben[/TD]
[/TR]
[/TABLE]
produkte (ca. 500.000 Datensätze)
[TABLE=“class: outer_border”]
[TR]
[TD]id[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Radio[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Fernseher[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hubschrauber[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Dampfmaschine[/TD]
[/TR]
[/TABLE]
zuordnung (ca. 5.000.000 Datensätze)
[TABLE=“class: outer_border”]
[TR]
[TD]id[/TD]
[TD]produkt_id[/TD]
[TD]bauteil_id[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
[/TABLE]
Soweit so simpel.
Ich möchte nun verschiedene Bauteile angeben können, und dann alle Produkte zurückbekommen, für die mindestens eines der Bauteile benötigt wird. Das ist auch ansich kein Problem, mit einem Limit (z.B. 1000) im Query braucht das wenige Millisekunden.
Aber wie man sich vorstellen kann, möchte ich nicht irgendwelche Datensätze, sondern ich möchte die Datensätze sortiert haben. Und zwar nach der Anzahl der für das jeweilige Produkt fehlenden Bauteile.
Tja, und weil dann für alle Treffer noch die Anzahl der fehlenden Items ermittelt werden muss, wird das ganze laaaaangsam. Wenn ich 100 Bauteile angebe, dauert es auf meinem Server (AMD Athlon II X4 Quad-Core 4x 2,3 GHz, 16 GB DDR3-RAM) ca. 3-5 Sekunden.
Mein Query:
WITH bauteil_ids AS (
SELECT id
FROM bauteile
WHERE id IN ((3145),(4466),(4323),(4452),(3095),(3195),(4131),(3140),(3189),(4512))
)
SELECT
produkt_id,
count(*) as gesamtbauteilcount,
SUM(CASE WHEN not EXISTS (SELECT id FROM ingredients WHERE id = ingredient_id)
THEN 1 ELSE 0 END) AS missingbauteilcount
FROM zuordnung
GROUP BY produkt_id
ORDER BY missingbauteilcount ASC, gesamtbauteilcount DESC
LIMIT 20
Meine Frage ist also:
- Kann ich am Query noch irgendwas wesentlich besser machen?
- Wo muss ich meinen Server aufrüsten, damit das ganze schneller wird? CPU, Ram? Ganz was anderes?
Mein Ziel ist, die ganze Abfrage auf <1 Sekunde zu bekommen, bei der oben angegebenen Anzahl an Datensätzen.
Vielen Dank für eure Hilfe!