✔ MYSQL - JOIN und LEFT JOIN

BlackPMetall · 10. September 2014

Hallo

,

ich arbeite gerade an eine Filterfunktion bei einem Online Shop. Dabei wird abhängig vom Auswahl eine virtuelle Tabelle mit JOIN zwischen mehrere Tabellen erstellt.

Es funktioniert alles soweit. Problem ist nur wenn zwei bestimmte Tabellen mit einander kombiniert werden, die MySql Anfrage sehr langsam läuft.

Wenn alle Filterfunktionen ausgewählt wurden, dann sieht die maximale Anfrage so aus:

select
p.products_image, p.products_image_lrg, pd.products_name, p.teppichnr, p.products_quantity, p.long, p.width, pricehoch, p.products_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price
from
products p
left join products_description pd on pd.products_id = p.products_id
left join specials s on s.products_id = p.products_id
left join products_to_categories p2c on p2c.products_id = p.products_id
join teppichfarbe tep on tep.teppichnr = p.products_id
join teppichform tef on tef.teppnr = p.products_id
where
p.products_status = '1' and p.long < '566' and p.width < '372' and ( tep.farbe = 'Rot' ) and ( tef.form = '0' ) and pd.language_id = '2' AND p.products_price < '19530.5'
group by p.products_id order by `so`
DESC , p.products_id DESC

Problem Tabellen sind: teppichfarbe und teppichform. Wenn diese beide miteinander in der Anfrage drin sind ist die Anfrage sehr langsam. Die Tabellen haben zurzeit um 10000 Einträge.

Struktur diese beide Tabellen sind wie folgt:
`teppichfarbe` (
`teppichnr` int(10) NOT NULL,
`farbe` varchar(15) NOT NULL
)

`teppichform` (
`teppnr` int(10) NOT NULL,
`form` int(10) NOT NULL
)

Kann mir jemand weiter helfen?

Vielen Dank in Voraus.

alxy · 10. September 2014

https://wikis.oracle.com/pages/viewpage.action?pageId=27263381

Ich würde auf jedenfall erstmal EXPLAIN benutzen.
Und dann würde ich noch nen Index auf die Spalten setzen, die in den JOINs vorkommen.

Haben deine Tabellen eigentlich alle keinen PRIMARY KEY?

BlackPMetall · 10. September 2014

Vielen Dank für die schnelle Antwort.

Struktur andere Tabellen sieht wie folg aus:

Code:

`products_description` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `products_name` varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `products_description` text CHARACTER SET latin1,
  `teppichnr` int(10) DEFAULT NULL,
  `lagernr` int(10) DEFAULT NULL,
  `art` int(11) DEFAULT NULL,
  `long` int(10) DEFAULT NULL,
  `width` int(10) DEFAULT NULL,
  `knoten` int(15) DEFAULT NULL,
  `quality` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `anderenamen` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `flor` int(5) DEFAULT NULL,
  `kette` int(5) DEFAULT NULL,
  `quali` int(10) DEFAULT NULL,
  `form` int(10) DEFAULT NULL,
  `muster` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `ursprung` int(5) DEFAULT NULL,
  `alter` int(5) DEFAULT NULL,
  `sizec` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `sizec2` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `products_url` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `products_viewed` int(5) DEFAULT '0',
  `products_head_title_tag` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  `products_head_desc_tag` longtext COLLATE utf8_unicode_ci,
  `products_head_keywords_tag` longtext COLLATE utf8_unicode_ci,
  `dicke` int(10) DEFAULT NULL,
  `farbton` enum('0','1') CHARACTER SET latin1 DEFAULT NULL,
  `seide` int(11) NOT NULL DEFAULT '0',
  `products_head_listing_text` longtext CHARACTER SET latin1 COLLATE latin1_german2_ci,
  `products_head_sub_text` longtext CHARACTER SET latin1 COLLATE latin1_german2_ci,
  PRIMARY KEY (`products_id`,`language_id`),
  KEY `products_name` (`products_name`)
)



`products` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_quantity` int(4) NOT NULL DEFAULT '0',
  `products_model` varchar(255) DEFAULT NULL,
  `products_image` varchar(64) DEFAULT NULL,
  `products_image_med` varchar(64) DEFAULT NULL,
  `products_image_lrg` varchar(64) DEFAULT NULL,
  `products_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
  `products_date_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `products_last_modified` datetime DEFAULT NULL,
  `products_date_available` datetime DEFAULT NULL,
  `products_weight` decimal(5,2) NOT NULL DEFAULT '0.00',
  `products_status` tinyint(1) NOT NULL DEFAULT '0',
  `products_tax_class_id` int(11) NOT NULL DEFAULT '0',
  `manufacturers_id` int(11) DEFAULT NULL,
  `products_ordered` int(11) NOT NULL DEFAULT '0',
  `products_shipping` int(11) DEFAULT NULL,
  `weekaction` enum('0','1','2','3') DEFAULT '0',
  `products_shipping_id` int(11) DEFAULT NULL,
  `teppid` varchar(30) DEFAULT NULL,
  `teppichnr` int(10) DEFAULT NULL,
  `lagernr` int(10) DEFAULT NULL,
  `art` int(10) DEFAULT NULL,
  `long` int(10) DEFAULT NULL,
  `width` int(10) DEFAULT NULL,
  `knoten` int(15) DEFAULT NULL,
  `flor` int(10) DEFAULT NULL,
  `kette` int(10) DEFAULT NULL,
  `quali` int(10) DEFAULT NULL,
  `form` int(10) DEFAULT NULL,
  `ursprung` int(10) DEFAULT NULL,
  `alter` int(10) DEFAULT NULL,
  `sizec` varchar(100) DEFAULT NULL,
  `sizec2` varchar(100) DEFAULT NULL,
  `dicke` int(10) DEFAULT NULL,
  `seide` int(10) DEFAULT NULL,
  `farbton` int(10) DEFAULT NULL,
  `so` int(5) NOT NULL DEFAULT '0',
  `pricehoch` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`products_id`),
  KEY `idx_products_date_added` (`products_date_added`)
)


`products_to_categories` (
  `products_id` int(11) NOT NULL,
  `categories_id` int(11) NOT NULL,
  PRIMARY KEY (`products_id`,`categories_id`)
)

`specials` (
  `specials_id` int(11) NOT NULL AUTO_INCREMENT,
  `products_id` int(11) NOT NULL,
  `specials_new_products_price` decimal(15,4) NOT NULL,
  `specials_date_added` datetime DEFAULT NULL,
  `specials_last_modified` datetime DEFAULT NULL,
  `expires_date` datetime DEFAULT NULL,
  `date_status_change` datetime DEFAULT NULL,
  `status` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`specials_id`),
  KEY `idx_specials_products_id` (`products_id`)
)

Habe EXPLAIN probiert aber danach hat die Anfrage leider nicht mehr funktioniert.

Ok, EXPLAIN Ergebniss ist jetzt da

Nach dem ich bei den beiden Tabellen die Indexe hinzugefügt habe, ist alles viel schneller geworden. Vielen Dank für den Hinweis.
Habe jetzt noch ein EXPLAIN gemacht. Gibt es noch andere Optimierungsmöglichkeiten?

BaseBallBatBoy · 10. September 2014

Einige erste kurze Beobachtungen/Fragen

products zu products_description ist 1:n? Oder warum left join?
products_description.products_id ist AUTO_INCREMENT?! und dieser join darauf funktioniert wirklich?? Ich hätte hier einen FK auf products.products_id erwartet... und auch einen eigenen products_description.products_description_id PK...
Was genau ist der Zweck von products und products_description ? Die haben ja praktisch die selben Felder?
DEFAULT NULL kannst du dir sparen, das macht er sowieso bei einer nullable column
Ich wäre etwas konsistenter mit der Benennung der Indizes mal mit idx_ und mal ohne ist nicht so hübsch
In deinem WHERE, alles was eine Zahl ist kannst du auch genau so angeben, z.B. p.products_price < 19530.5 anstatt p.products_price < '19530.5'
Warum group by? Du hast keine aggregats Funktion. Weg damit.
Was bringt dir genau order by `so` DESC ?
Wozu der join auf products_to_categories? Du verwendest gar nichts von der Tabelle und du filters auch nicht indirekt da es ja ein left join ist.

BlackPMetall · 10. September 2014

Danke für deine Hilfe.
Zu deinen Fragen:

products_description.products_id ist AUTO_INCREMENT?! und dieser join darauf funktioniert wirklich?? Ich hätte hier einen FK auf products.products_id erwartet... und auch einen eigenen products_description.products_description_id PK...
Was genau ist der Zweck von products und products_description ? Die haben ja praktisch die selben Felder?

Die Einträge in products_description und products werden gleichzeitig erstellt und deshalb passen zusammen. In Felder, die bei beiden Tabellen gleich sind, werden von mir noch von products_description gelöscht. Die werden ab jetzt nur in products existieren. Da der Shop in verschiedene Sprachen existiert, wird für jedes Produkt pro Sprache einen Eintrag gemacht mit gleichen products_id aber unterschiedlichen language_id.

Warum group by? Du hast keine aggregats Funktion. Weg damit.

Wenn ich Group by weg nehme, dann werden die Produkte mehrmals ausgegeben.

Was bringt dir genau order by `so` DESC ?

Ist eine zufällige Nummerierung, die ich Woche für Woche neu generiere, damit die Produkte nicht immer gleiche Sortierung haben.

Wozu der join auf products_to_categories? Du verwendest gar nichts von der Tabelle und du filters auch nicht indirekt da es ja ein left join ist.

Wenn bei dem Filter auch eine Katergorie ausgewählt wird, dann wir diese Tabelle auch genutzt.

Sonst ist alles Ok?

BaseBallBatBoy · 10. September 2014

Also in dem Fall ist gibt es die selbe product_id immer in beiden Tabellen products und products_description. Daher kannst du ja auch einen inner join machen.

BlackPMetall hat gesagt.:
Warum group by? Du hast keine aggregats Funktion. Weg damit.
Wenn ich Group by weg nehme, dann werden die Produkte mehrmals ausgegeben.

Dazu sollte group by nicht verwendet werden. Wenn du Duplikate entfernen willst, verwende stattdessen DISTINCT (http://www.mysqltutorial.org/mysql-distinct.aspx)

BlackPMetall hat gesagt.:
Wozu der join auf products_to_categories? Du verwendest gar nichts von der Tabelle und du filters auch nicht indirekt da es ja ein left join ist.
Wenn bei dem Filter auch eine Kategorie ausgewählt wird, dann wir diese Tabelle auch genutzt.

Wie jetzt? Ganz oben hast du geschrieben "Wenn alle Filterfunktionen ausgewählt wurden, dann sieht die maximale Anfrage so aus:". Und in dieser Query war kein solcher Filter drin.

Ich vermute mal, dass du in deiner Applikation die Query dynamisch erstellen lässt. In dem Fall würde ich dir empfehlen die products_to_categories nur in die Query mit aufzunehmen wenn auch tatsächlich darauf gefiltert werden soll.

Wie schon alxy geschrieben hat, ein Index/FK auf den Join Attributen sollte das ganze auch schneller machen.

So wie du teppichfarbe und teppichform joinst, muss ich davon ausgehen, dass darin für jede product_id ein Record existiert, korrekt? Sprich sie hat genau gleich viele rows wie die product Tabelle selber. Daher musst du besonders auf diese beiden id's einen Index legen.
Aber die grundsätzliche Frage dabei lautet dann eigentlich, warum hast du nicht die products Tabelle um eine weitere Spalte ergänzt für die Farbe und die Form? Dann hast du darin die Id welche z.B. eine Farbe repräsentiert. Entsprechend würden die teppich-tabellen viel weniger rows haben. Aber ich vermute mal es ist zu spät um Design Änderungen vorzunehmen.

BlackPMetall · 11. September 2014

Ich habe jetzt alles umgesetzt:

select distinct p.products_image, p.products_image_lrg, pd.products_name, p.teppichnr, p.products_quantity, p.long, p.width, pricehoch, p.products_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p left join products_description pd on pd.products_id = p.products_id left join specials s on s.products_id = p.products_id left join products_to_categories p2c on p2c.products_id = p.products_id join teppichfarbe tep on tep.teppichnr = p.products_id join teppichform tef on tef.teppnr = p.products_id where p.products_status = '1' and p.long > '82' and p.long < '599' and p.width > '54' and p.width < '405' and ( tep.farbe = 'Creme' or tep.farbe = 'Blau' ) and ( tef.form = '0' or tef.form = '3' ) and pd.language_id = '2' AND p.products_price > '824.4' AND p.products_price < '20540.3' AND ( `categories_id` = '24' OR `categories_id` = '27' OR `categories_id` = '30' ) order by `so` DESC , p.products_id DESC

Hier auch der EXPLAIN Anfrage:

Macht wirklich district und group by ein Unterschied?

Wie jetzt? Ganz oben hast du geschrieben "Wenn alle Filterfunktionen ausgewählt wurden, dann sieht die maximale Anfrage so aus:". Und in dieser Query war kein solcher Filter drin.

Bei der ersten Anfrage hatte ich nur vergessen gehabt die Kategorien auch auszuwählen, alles andere war schon richtig.

Ja die Query wird dynamisch generiert. Filter wird nach jeder Änderung per jquery gelesen und per ajax und php neu generiert.

Die Tabelle Farben, Form und Categories habe ich extra angelegt, da manche Produkte mehrere Farben bzw. Formen oder Kategorien haben können. D.h. bei diesen Tabellen gibt es manchmal mehrere Einträge mit dem selben ID.

Jetzt läuft die Anfrage innerhalb eine Sekunde. Vielen Dank.
Habe ich jetzt alles richtig?

BaseBallBatBoy · 11. September 2014

BlackPMetall hat gesagt.:
Macht wirklich district und group by ein Unterschied?

Ja. MySQL verzeiht dir diese Query mit Group By und führt sie aus. Alle anderen Datenbanken würden dir sagen die Query sei falsch und würden sie nicht ausführen. Distinct wird verwendet um Duplikate zu entfernen. Group By wird nur dann verwendet wenn man auch Aggregate verwendet (also SUM, MIN, MAX, AVG, COUNT, ...) (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html)
Wenn du einen Nagel hast, verwendest du ja auch keinen Schraubenzieher um den Nagel einzuschlagen sondern nimmst gleich den Hammer.

Was du evtl. noch machen kannst ist IN verwenden.

Anstelle:

SQL:

...
AND(`categories_id`='24'
OR`categories_id`='27'
OR`categories_id`='30')
...

Mit IN:

SQL:

....
AND categories_id IN (24,27,30)
...

Obwohl ich denke nicht dass dies einen performance impact haben wird.

Und wie schon einmal geschrieben, wenn z.B. categories_id ein Integer ist, schreibe nicht categories_id = '27' sondern categories_id = 27. Wenn du nämlich '27' schreibst, muss MySQL intern ein Cast von Varchar zu einem Integer machen. Das kostet zwar nicht viel, aber es kostet etwas. Also warum sollte die DB einen überflüssigen Cast ausführen müssen wenn es auch ohne geht?

BlackPMetall · 11. September 2014

Vielen Dank an BaseBallBoy und alxy. Ihr habt mir wirklich weiter geholfen. Problem gelöst

.
Die Query läuft jetzt super.

Habe jetzt ein neues Problem mit Javscript/jQuery (Doppelte Content). Habe aber ein neues Thema dafür geöffnet

.

✔ MYSQL - JOIN und LEFT JOIN

BlackPMetall

Grünschnabel

alxy

Erfahrenes Mitglied

BlackPMetall

Grünschnabel

BaseBallBatBoy

Erfahrenes Mitglied

BlackPMetall

Grünschnabel

BaseBallBatBoy

Erfahrenes Mitglied

BlackPMetall

Grünschnabel

BaseBallBatBoy

Erfahrenes Mitglied

BlackPMetall

Grünschnabel

Neue Beiträge