Abfrage schlecht aufgebaut?

[Helmo]

Hi,

ich habe ein Problem mit einer mySQL-Abfrage (mySQL 5.0).
Sie dauert bei mir einfach zu lange, da ich eine scheinbar sehr ungünstige Lösung von Subqueries gewählt habe :D (13 Sekunden Laufzeit).

Ich habe folgendes Szenario.

Eine Tabelle "articles" mit ca 200.000 Datensätzen hat ein Spalte "artnum". Die Datensätze (Spalte "artnum") sehen ca so aus:
art03489-green
art03489-red
art48821-orange
...

Wie man leicht vermuten kann, handelt es sich dabei um Artikelnummern, wobei ein Artikel in verschiedenen Farben erhältlich ist (red, green, orange). Die eigentlich Nummer (zB art03489) bleibt bei farblich unterschieldichen aber sonst gleichen Artikeln ebenfalls gleich.

Nun wollte ich auf einer Seite die 20 beliebtesten Artikel anzeigen lassen. Dazu speichere ich in einer Tabelle "favs" die Nummern (ohne Farbe) in der Spalte "article".
Die Datensätze sehen demnach wie folgt aus:
art03489
art48821
...

Und jetzt kommt mein Problem :-)
Ich möchte auf dieser Seite 20 Datensätze aus der Tabelle "favs" auswählen. Die Datensätze möchte ich noch um die drei Felder "green", "red" und "orange" erweitern. Je nachdem ob ein Artikel in der Farbe existiert, soll in der entsprechenden Spalte eine 1 oder eine 0 stehen. Folgendermaßen soll das ganze dann bspw aussehen (Spalten durch ";" getrennt):

article;red;green;orange
art03489;1;1;0
art48821;0;0;1

Mein SQL-Query sah bisher so aus, braucht aber durch die ganzen Subqueries ganze 13 Sekunden :X

SELECT
article,
(SELECT IF((EXISTS(SELECT * FROM articles WHERE artnum LIKE CONCAT(article,'%red'))),1,0)) AS red,
(SELECT IF((EXISTS(SELECT * FROM articles WHERE artnum LIKE CONCAT(article,'%green'))),1,0)) as green,
(SELECT IF((EXISTS(SELECT * FROM articles WHERE artnum LIKE CONCAT(article,'%orange'))),1,0)) as orange
FROM fav
ORDER BY clicks DESC LIMIT 20

Hat jemand eine Idee, wie ich das ganze beschleunigen kann?

Gruss
Helmo

[Cheatah]

Hi,

SELECT
article,
(SELECT IF((EXISTS(SELECT * FROM articles WHERE artnum LIKE CONCAT(article,'%red'))),1,0)) AS red,
(SELECT IF((EXISTS(SELECT * FROM articles WHERE artnum LIKE CONCAT(article,'%green'))),1,0)) as green,
(SELECT IF((EXISTS(SELECT * FROM articles WHERE artnum LIKE CONCAT(article,'%orange'))),1,0)) as orange
FROM fav
ORDER BY clicks DESC LIMIT 20

Hat jemand eine Idee, wie ich das ganze beschleunigen kann?

ersetze erst mal das "%" durch ein "-".

Cheatah

[Helmo]

ersetze erst mal das "%" durch ein "-".

Das hat leider nichts gebracht (oder vllt 1 Sek), wobei ich korrigieren muss, dass es gar nicht 13, sondern 6 Sekunden sind (Hatte die 13 von gestern noch im Kopf).

Ich habe auch mal versucht, das "LIKE" durch "=" zu ersetzen, aber dann wird er knapp eine Sekunde langsamer.

Gibt es vllt eine Lösung ohne die ganzen UnterQueries oder eine andere Lösung? :-)

[Ilja]

yo,

Nun wollte ich auf einer Seite die 20 beliebtesten Artikel anzeigen lassen. Dazu speichere ich in einer Tabelle "favs" die Nummern (ohne Farbe) in der Spalte "article".

alleine diese speicherung scheint mir schon eine denormalisierung zu sein. das ist grundsätzlich kein beinbruch, sollte aber bewußt geschehen. die frage ist, woran erkennt man den die "beliebtesten artikel" und ob man diese artikel noch extra persisiteren muss oder nicht schon aus den vorhanden daten herleiten kann. aber nun zu deinem problem.

ich das CASE konstrukt einsetzen, das ließt meienr meinnung nach besser als deine schreibweise, zumal ich auch SELECT und klammern in deienr abfrage für überflüssig halten. zum anderen sehe ich zwei ausführungsprobleme in deiner abfrage.

zum einen benutzt du bei der korrelation in der unterabfrage funktionen. dadruch kann ein eventuell vorhandener index nicht greifen, jendefalls nicht solange das dbms keine funktionallen indexe kennt. diese zu beheben erfordert ein wenig mehr wissen über die beiden tabellen fav und article und wie die beiden tabellen genau miteinander verbunden sind. wäre gut, wenn du dazu noch ein paar infos geben könntest.

zum anderen erscheint es mir, als wenn erst mal alle datensätze von fav genommen werden udn darauf eben immer korrelierte unterabfragen ausgeführt werden und dann erst die anzahl auf 20 begrentzt wird. das liegt an der tatsache, dass er erst einmal die ergebnisse alle ergebnisse der tabelle fav haben will, um sie dann zu sortieren und die 20 ersten entsprechend auszuwählen. um dieses zu testen könntest du nur mal testweise das ORDER BY weglassen udn nur das LIMIT verwenden. sollte dies wirklich ein problem sein, kannst du zuerst in der FROM klausel eine unterabfrage einbauen, die dir zuerst die 20 datensätze aus der tabelle fav liefert, ohne die korrleirerte unterabfrage auszuführen und dann erst später im äußen SELECT diese wieder hinzugefügt wird.

Ilja

[Helmo]

alleine diese speicherung scheint mir schon eine denormalisierung zu sein. das ist grundsätzlich kein beinbruch, sollte aber bewußt geschehen. die frage ist, woran erkennt man den die "beliebtesten artikel" und ob man diese artikel noch extra persisiteren muss oder nicht schon aus den vorhanden daten herleiten kann. aber nun zu deinem problem.

Also ein Problem ist, dass alle paar Minuten neue Artikel (zB neue Farben) in die Liste aufgenommen werden. Ebenso kann auch von einer Minute auf die andere eine Farbe wieder aus der Tabelle verschwinden. Deswegen kann ich die verfügbaren Farben nicht direkt in der favs-Tabelle speichern, da diese dynamisch ausgelesen werden müssen.

zum einen benutzt du bei der korrelation in der unterabfrage funktionen. dadruch kann ein eventuell vorhandener index nicht greifen, jendefalls nicht solange das dbms keine funktionallen indexe kennt. diese zu beheben erfordert ein wenig mehr wissen über die beiden tabellen fav und article und wie die beiden tabellen genau miteinander verbunden sind. wäre gut, wenn du dazu noch ein paar infos geben könntest.

Direkt verbunden sind 2 Datensätze eben leider nicht. In der Tabelle articles kann ein Datensatz einer Farbe auch mehrfach vorkommen, da der Artikel in verschiedenen Shops zur Verfügung steht.
Man kann sie demnach nur über den Artikelnamen (ohne Farbe) zuweisen.

zum anderen erscheint es mir, als wenn erst mal alle datensätze von fav genommen werden udn darauf eben immer korrelierte unterabfragen ausgeführt werden und dann erst die anzahl auf 20 begrentzt wird. das liegt an der tatsache, dass er erst einmal die ergebnisse alle ergebnisse der tabelle fav haben will, um sie dann zu sortieren und die 20 ersten entsprechend auszuwählen. um dieses zu testen könntest du nur mal testweise das ORDER BY weglassen udn nur das LIMIT verwenden. sollte dies wirklich ein problem sein, kannst du zuerst in der FROM klausel eine unterabfrage einbauen, die dir zuerst die 20 datensätze aus der tabelle fav liefert, ohne die korrleirerte unterabfrage auszuführen und dann erst später im äußen SELECT diese wieder hinzugefügt wird.

Das mit der Unterabfrage habe ich versucht. Das bringt leider keinen Geschwindigkeitsvorteil.

Gruss
Helmo

[Ilja]

yo,

Also ein Problem ist, dass alle paar Minuten neue Artikel (zB neue Farben) in die Liste aufgenommen werden. Ebenso kann auch von einer Minute auf die andere eine Farbe wieder aus der Tabelle verschwinden. Deswegen kann ich die verfügbaren Farben nicht direkt in der favs-Tabelle speichern, da diese dynamisch ausgelesen werden müssen.

wie ich bereits schrieb, es ist erst einmal die frage, wie sich ein fav definiert und dann überlegt, ob man sie persitieren muss oder nicht aus den schon vorhanden informationen herleiten kann. was das problem mit den dynamischen farben angeht, so ist das eine frage des designs. wählt man es geschickt, kann man auch dem problem entgegen treten. das ist auch wichtig, sonst müsstest du die abfrage ja immer entsprechend der farben anpassen und das wäre sehr unvorteilhaft. als beispiel nehme ich mal zwei artikel von dir.

• art03489-green
• art03489-red

die erste frage ist, ob die eigenschaft rot oder grün auch zum artiklennamen dazugehören. wenn ja beläßt man sie dort, wenn nein, nimmt man sie raus. auf jeden fall würde ich aber die farbe noch einmal extra speichern, wo nur die farbe drinne steht. je nachdem wie deine vorgaben sind in eine extra tabelle. so könnte es den artikel art03489 nur einmal in der artikeltabelle geben und eine zusätzliche taballe, welche die farbvariationen speichert. das muss aber nicht so sein. es gibt eben nicht das eine richtige design, sondern es kommt immer auf deine fachlichkeit drauf an, die du widergeben willst.

wie auch immer scheint mir die ursache deines problems der langen laufzeit deiner abfrage im design zu sein. die frage ist, ob du die möglichkeiten hast, dieses zu ändern. falls nein, solltest du uns noch mal einen genauen einblick in die details deiner relevanten tabellen geben, sprich wie genau sind sie aufgebaut, welche beispieldaten enthalten sie und wie sollte das ergebnis der abfrage aussehen.

Ilja