Best-Practice für multiple Filterselektion mit der selben Subquery gesucht

[cpassion]

Hallo zusammen,

ich habe ein kleines Problem beim erstellen eines E-Commerce Portals, auf Basis von symfony3.

Hier ist ein Beispiel der zugrundeliegenden Tabelle auf MySQL Seite:

Wie man sieht, existiert nur eine Produkttabelle, woher ich Daten selektiere. Keine umständlichen Joins zwischen verschiedenen Tabellen, welche der Geschwindigkeit schaden könnten.

Hier ist ein kleines Mockup des aktuellen Standes, den ich habe um zu verdeutlichen was ich rendern möchte:

Der Anwendungsfall ist folgender: ich möchte eine gefilterte Produktansicht anzeigen (zum Beispiel "zeige mir alle Produkte mit der Farbe grün"). Anschließend erhält man die entsprechenden Produkte (roter Bereich im Mockup) + sowie in der Sidebar die Möglichkeit weitere Filter auf Basis der bereits gefilterten Produktcollection anzuwenden (z. B. Marken oder Größen, in der Praxis gibt es noch wesentlich mehr Filtermöglichkeiten; gelber Bereich).

Das Problem dabei ist, wie man sehen kann, dass ich für jedes Ergebnis eine andere angepasste Query nutze (z. B. in der Sidebar mit verschiedenen "GROUP BY" Statements), jedoch darin immer die selbe Subquery eingesetzt wird ("FROM products WHERE [aktuelle Filter]"), welche die aktuelle Produktcollection darstellt.

Dieser Umstand drückt die Geschwindigkeit meiner Seite immens (über eine Million Produkte in der Datenbank und ich brauche ca. 7-8 mal die eigentlich selbe Query mit jeweils leicht abgeänderten Parametern).

Ich habe schon eine Lösung gefunden, die leider für mich nicht in Frage kommt. Das wäre der Umstieg der Umgebung auf MariaDB, welches Subquery Caching ermöglicht. Dieser Umstieg ist in der aktuellen Umgebung leider nicht möglich. Ein Caching durch MySQL ist bereits aktiviert.

Kann mir irgendjemand ein Paar Hinweise oder Hilfestellungen geben, wie ich es schaffe auf clevere Art und Weise die Queries zu vermindern, bzw. den kompletten Prozess ordentlich zu verschnellern. Gibt es da eine bessere Möglichkeit, als wie ich es aktuell mache? Wie würdet ihr da heran gehen?

Vielen Dank schon einmal für euere Hilfe!

[fanderlf]

Bin kein riesen MySQL Experte, aber ich vermute dass das mit einer ordinaeren SQL Datenbank gar nicht geht. Fuer diese Art von Problem gibt es spezielle Suchindexe die solche Probleme wesentlich effizienter loesen. Einer davon ist z.B. Elasticsearch was wir bei uns in der Firma genau fuer diese Problem verwenden. Die Gruppierung nach Kategorien und das Zaehlen nennt man uebrigens Facettierung (englisch facets). Vielleicht kannst Du das ja mal im Zusammenhang mit MySQL googlen.

[defo]

Das nenne ich mal eine tolle Anfrage!

Dennoch fehlt mir eine Beispielabfrage.

Stichwort wäre dennoch für mich die SubQuery Thematik.
Wie sieht das aus? Klassischer Weise sind die Subqueries lahm, wenn Sie im Select selbst eingebaut sind und joinen auf die die Hauptabfragemenge (From Clause). Das scheint mir aber bei Dir nicht der Fall, weil es nach meinem Verständnis keinen Sinn machen würde.

Zeig doch mal ein (langsames) Beispiel.
So oder so wäre der Weg wahrscheinlich, aus subqueries "saubere" joins zu machen.

P.S: Ich meine das effektive, abgesetzte SQL, keine Einzelteile.

[cpassion]

Vielen Dank für euere Antworten.

Hier mal ein Paar Beispielqueries, die erzeugt werden:
Für die Produkte

SELECT 
  p0_.id AS id_0, 
  p0_.page_id AS page_id_1, 
  p0_.merchant AS merchant_2, 
  p0_.merchant_id AS merchant_id_3, 
  p0_.title AS title_4, 
  p0_.description AS description_5, 
  p0_.image AS image_6, 
  p0_.price AS price_7, 
  p0_.currency AS currency_8, 
  p0_.url AS url_9, 
  p0_.brand AS brand_10, 
  p0_.color AS color_11, 
  p0_.size AS size_12, 
  p0_.quality AS quality_13, 
  p0_.hash AS hash_14, 
  p0_.last_update AS last_update_15, 
  p0_.timestamp AS timestamp_16 
FROM 
  product p0_ 
WHERE 
  p0_.page_id IN (?) 
ORDER BY 
  p0_.last_update DESC 
LIMIT 
  18 OFFSET 0

Für die Filter:

SELECT 
  p0_.brand AS brand_0, 
  count(p0_.brand) AS sclr_1 
FROM 
  product p0_ 
WHERE 
  p0_.page_id IN (?) 
GROUP BY 
  p0_.brand 
ORDER BY 
  p0_.brand ASC, 
  p0_.title ASC

SELECT 
  p0_.color AS color_0, 
  count(p0_.color) AS sclr_1 
FROM 
  product p0_ 
WHERE 
  p0_.page_id IN (?) 
GROUP BY 
  p0_.color 
ORDER BY 
  p0_.color ASC, 
  p0_.title ASC

[Ralf Jansen]

Wo sind denn da jetzt Subqueries? Oder trägt das Feature nur im Namen den Bezeichner subquery macht aber was anderes?
Oder sollte das ? in der in Klausel durch eine Subquery ersetzt werden?

[defo]

Wo sind denn da jetzt Subqueries? Oder trägt das Feature nur im Namen den Bezeichner subquery macht aber was anderes?
Oder sollte das ? in der in Klausel durch eine Subquery ersetzt werden?

Ja wo laufen sie denn?!
Ich bin auch verwirrt, mglw ist das Zauberwort symfony3, das kenne ich nicht. Mglw. baut das die Abfragen zusammen?
Wenn es so ist, und man das nicht beeinflussen kann, bin ich raus.