✔ [MySQL] dependent subquery auflösen

[aGeNET]

Servus,

seit MySQL 5.1.x verursachen die dependent subqueries wegen eines Bugs extreme Performanceprobleme und führen bis zum Totalausfall des Servers. In MySQL 5.6 soll der Fehler behoben sein - bis die aber zum Standard wird, vergehen sicher noch einige Jahre.

Per EXPLAIN wird die Subquery über die products_images Tabelle als dependet subquery deklariert:

SELECT p.products_id, pd.products_name, (
  SELECT pi.images_file
  FROM products_images pi
  WHERE pi.products_id = p.products_id
  LIMIT 1
) AS products_image
FROM products p
LEFT JOIN products_description pd 
ON ( p.products_id = pd.products_id AND pd.language_id = '2' )
LEFT JOIN products_to_categories p2c
ON p2c.products_id = p.products_id
WHERE p.products_status = '1'
AND p2c.categories_id NOT
IN ( 1, 2, 3, ..., n );

Unter MySQL 4.1 und 5.0 hat das ohne Probleme funktioniert.

Da ich kein MySQL-Profi bin, brauche ich einen Rat wie ich per JOINs in möglichst einer Query das gleiche Ergebnis bekomme.

Wenn es aus der Situation keinen Ausweg gibt, muss ich die products_images Tabelle in einer seperaten Query auslesen - diesen Weg würde ich aber nur ungern gehen, da ich die Anzahl der Datenbankabfragen pro Skriptlauf möglichst gering halten will.

MfG aGeNET

 

[Kalito]

Hallo,

du kannst doch das ebenfalls als Left Join machen

Select p.products_id, pd.products_name, pi.images_file
FROM products p
LEFT  JOIN products_images pi ON pi.products_id = p.products_id
LEFT JOIN products_description pd ON ( p.products_id = pd.products_id AND pd.language_id = '2' )
LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id
WHERE p.products_status = '1' AND p2c.categories_id NOT IN ( 1, 2, 3, ..., n );

Gruß

 

[aGeNET]

Moin,

ich hatte ja erst Zweifel, da der LEFT JOIN ja Duplikate für jeden Bildeintrag erstellt.
Mit einem GROUP BY pi.products_id klappt das nun aber zumindest wie gewünscht:

SELECT p.products_id, pd.products_name, pi.images_file
FROM products p
LEFT JOIN products_images pi ON pi.products_id = p.products_id
LEFT JOIN products_description pd ON ( p.products_id = pd.products_id AND pd.language_id = '2' )
LEFT JOIN products_to_categories p2c ON p2c.products_id = p.products_id
WHERE p.products_status = '1' AND p2c.categories_id NOT IN (1, 2, 3) GROUP BY pi.products_id;

MfG aGeNET

 

[Yaslaw]

Der 'GROUP BY pi.products_id' wird aber freundlicherweise von MySQL zu einem 'p.products_id, pd.products_name, pi.images_file' erweitert, da für die restlichen Felder keine Gruppenfunktionen angegeben sind. Das wird bei anderen Datenbanken zu Fehler führen.

Sauberer währe auf den GROUP BY zu verzichten und ein DISTINCT einzusetzen

item: Nebenbei. in den JOIN gehören niemals feste bedinungen. Das "pd.language_id = '2'" gehört ganz klar in den WHERE-Block

item: Ich geh mal davon aus , dass pd.language_id und p.products_status Zahlenfelder sind und keine Strings - ergo auch so behandeln und darum die Werte ohne ' schreiben

item: Ich empfehle dir dein SQL auf mehrere Zeilen zu verteilen und mit Tabulatoren zu arbeiten - es wird übersichtlicher. (Und im Forum gleich die SQL-Tags verwenden)

item: In den ON-Bdinnungen würde ich immer zuerst das Feld der Haubtquelle nehmen und dann erst das andere. Es wird lesbarer

SELECT DISTINCT
	p.products_id, 
	pd.products_name, 
	pi.images_file
FROM 
	products 								p
	LEFT JOIN products_images 				pi 
		ON p.products_id = pi.products_id
	LEFT JOIN products_description 			pd 
		ON p.products_id = pd.products_id 
	LEFT JOIN products_to_categories 		p2c 
		ON p.products_id = p2c.products_id
WHERE 
	pd.language_id = 2
	AND p.products_status = 1
	AND p2c.categories_id NOT IN (1, 2, 3)
;

 

[aGeNET]

Danke Yaslaw für die tiefergehenden Ratschläge.
Ich werde für das GROUP BY die Funktion MIN(pi.images_id) integrieren, damit es a) sauberes SQL ist und b) gewährleistet das gleichzeitig den Zugriff auf den ersten Bildeintrag je Produkt.

Ein DISTINCT auf die products_images scheidet meines Erachtens nach aus, da nicht immer Einträge in dieser Tabelle vorhanden sind, ich die eigentlichen Produktdaten (products, products_description, products_to_categories) aber immer brauche.
Zwecks fehlender Kenntnisse kann das auch eine Fehleinschätzung meinerseits sein.

SELECT
    p.products_id, 
    pd.products_name, 
    pi.images_file,
    MIN(pi.images_id)
FROM 
    products                                p
    LEFT JOIN products_images               pi 
        ON p.products_id = pi.products_id
    LEFT JOIN products_description          pd 
        ON p.products_id = pd.products_id 
    LEFT JOIN products_to_categories        p2c 
        ON p.products_id = p2c.products_id
WHERE 
    pd.language_id = 2
    AND p.products_status = 1
    AND p2c.categories_id NOT IN (1, 2, 3)
GROUP BY
    pi.products_id
;

 

[Yaslaw]

Zum sauber bleiben solltest du alle Spalten die du ohne Group-Funktion im SELECT-Teil hast auch im GROUP BY-Teil auflisten. MySQL macht das automatisch so - aber du solltest es dir nicht angewöhnen darum zu machen da Orcale etc. bei sowas einen Fehler generieren.

SELECT
    p.products_id, 
    pd.products_name, 
    pi.images_file,
    MIN(pi.images_id)
FROM 
    ....
GROUP BY
    p.products_id, 
    pd.products_name, 
    pi.images_file