nicht angemeldet
Explain (me ;-) )
Hallo alle
also nach langen hin und her hab ich eine ganze tolle Abfrage gefunden für das leidige Problem, die letzen Nachrichten und deren Benutzer dazu aus einer Nachrichten (Forums) DB zu lesen.
ein Verständnissproblem existiert noch - zugegeben ich habe es noch nicht 100% durchschaut wie das mit den join funktioniert - ich benutze einen left join es klappen beide Versionen, d.h. ich kann beide Tabellen links joinen.
Die Variante 1 bringt bei einem EXPALIN folgendes:
table type possible_keys key key_len ref rows Extra
m index t_id t_id 5 NULL 86 Using temporary; Using filesort
t eq_ref PRIMARY,b_id PRIMARY 4 m.t_id 1 Using where
Die 2. Variante:
table type possible_keys key key_len ref rows Extra
t ref b_id b_id 4 const 10 Using temporary; Using filesort
m ref t_id t_id 5 t.t_id 11
Mein Gefühl sgat mir die 2. ist besser, oder?
Struppi.
-
Darf ich mal die dazugehörigen SQL-Statements sehen?
-
Darf ich mal die dazugehörigen SQL-Statements sehen?
SELECT SUBSTRING( MAX( CONCAT( m.date, m.user)), 20) AS lastUser, MAX(m.date) as lastDate, t.*, COUNT(m.m_id) as msg, DATE_FORMAT(t.date,"%d.%m.%Y %H:%i") as date_f
FROM FORUM_thread t LEFT JOIN FORUM_msg m ON t.t_id = m.t_id
WHERE t.b_id=29
GROUP BY m.t_id
ORDER BY lastDate DESC
LIMIT 0, 10das ist die 2. Variante, bei der 1. sind einfach die tabellen thread und msg vertauscht
Struppi.
-
Hallo,
SELECT ...
FROM FORUM_thread t LEFT JOIN FORUM_msg m ON t.t_id = m.t_id
das ist die 2. Variante, bei der 1. sind einfach die tabellen thread und msg vertauschtAlso so?:
SELECT ...
FROM FORUM_msg m LEFT JOIN FORUM_thread t ON t.t_id = m.t_idUnd beide Resultsets sind identisch?
Dann kannst Du einen INNER JOIN nutzen. Der ist sicherlich besser optimiert als OUTER JOINs.SELECT ...
FROM FORUM_msg m INNER JOIN FORUM_thread t ON t.t_id = m.t_idOUTER JOINs benötigt man nur, wenn von einer Tabelle auch die Datensätze mit im Resultset sein sollen, für welche es keine Entsprechung in der anderen Tabelle gibt. Bei LEFT JOIN sind immer alle Datensätze der linken Tabelle im Resultset.
viele Grüße
Axel
-
OUTER JOINs benötigt man nur, wenn von einer Tabelle auch die Datensätze mit im Resultset sein sollen, für welche es keine Entsprechung in der anderen Tabelle gibt. Bei LEFT JOIN sind immer alle Datensätze der linken Tabelle im Resultset.
Dann würde ich sagen, ich brauche die 2. Variante. Ich will alle Resultate aus t und nur die Suchergebnisse bzw. max() aus m
Aber wenn ich jetzt inner join verwende:
table type possible_keys key key_len ref rows Extra
m index t_id t_id 5 NULL 86 Using temporary; Using filesort
t eq_ref PRIMARY,b_id PRIMARY 4 m.t_id 1 Using whereist die Zeile rows wieder bei 86 und da m die größere Tabelle werden wird. scheint mir es sinnvoll auf t zu joinen (sagt man das so?)
Struppi.
-
Hallo,
OUTER JOINs benötigt man nur, wenn von einer Tabelle auch die Datensätze mit im Resultset sein sollen, für welche es keine Entsprechung in der anderen Tabelle gibt. Bei LEFT JOIN sind immer alle Datensätze der linken Tabelle im Resultset.
Dann würde ich sagen, ich brauche die 2. Variante. Ich will alle Resultate aus t und nur die Suchergebnisse bzw. max() aus m
Meinst Du damit jetzt das t.* oder meinst Du alle _Datensätze_ aus t und nur die mit der Join-Bedingung übereinstimmenden Datensätze aus m? Im letzem Fall _musst_ Du "t LEFT JOIN m" verwenden oder "m RIGHT JOIN t". Diese beiden Varianten könntest Du mit EXPLAIN testen.
ist die Zeile rows wieder bei 86 und da m die größere Tabelle werden wird. scheint mir es sinnvoll auf t zu joinen (sagt man das so?)
Lies: http://dev.mysql.com/doc/mysql/en/explain.html. Dort steht die Bedeutung der EXPLAIN-Ausgaben. Wichtigstes Kriterium für JOINs ist der type. Hier ist eq_ref optimal. Bei einem OUTER JOIN ist die Kombination: eine Tabelle typ eq_ref, die andere Tabelle typ index, faktisch nicht zu überbieten. Zweites Kriterium ist, ob und welche Indizes genutzt werden. Dann erst kann geschaut werden, wieviele Zeilen _wahrscheinlich_ betrachtet werden müssen.
viele Grüße
Axel
-
Dann würde ich sagen, ich brauche die 2. Variante. Ich will alle Resultate aus t und nur die Suchergebnisse bzw. max() aus m
Meinst Du damit jetzt das t.* oder meinst Du alle _Datensätze_ aus t und nur die mit der Join-Bedingung übereinstimmenden Datensätze aus m? Im letzem Fall _musst_ Du "t LEFT JOIN m" verwenden oder "m RIGHT JOIN t". Diese beiden Varianten könntest Du mit EXPLAIN testen.Ohje, mir ist diese Ausdrucksweise nicht so geläufig, ich muss erstmal nachdenken:
Also ich brauch alle t.* auf die die WHERE Bedingung zutrifft und zusätzlich die MAX und COUNT werte aus m. die mit der Joinbedingung übereinstimmen.
Ob LEFT Oder R'IGHT macht keinen Unterschied.
ist die Zeile rows wieder bei 86 und da m die größere Tabelle werden wird. scheint mir es sinnvoll auf t zu joinen (sagt man das so?)
Lies: http://dev.mysql.com/doc/mysql/en/explain.html. Dort steht die Bedeutung der EXPLAIN-Ausgaben. Wichtigstes Kriterium für JOINs ist der type. Hier ist eq_ref optimal. Bei einem OUTER JOIN ist die Kombination: eine Tabelle typ eq_ref, die andere Tabelle typ index, faktisch nicht zu überbieten. Zweites Kriterium ist, ob und welche Indizes genutzt werden. Dann erst kann geschaut werden, wieviele Zeilen _wahrscheinlich_ betrachtet werden müssen.Die Seite hatte ich schon gelesen. Ist aber natürlich schwer zu verstehen wenn man die Begriffe nicht alle kennt.
Mich hatte interesert was das wichtigste an der Ausgabe von EXPLAIN ist. Und jetzt hab ich's endlich erklärt bekommen, danke.
Ich hab als Ergebnis nur zwei 'ref' aber ich vermute mal das geht nicht besser.
Struppi.
-
-
-
-
yo,
SELECT ... DATE_FORMAT(t.date,"%d.%m.%Y %H:%i") as date_f
FROM FORUM_thread t LEFT JOIN FORUM_msg m ON t.t_id = m.t_id
WHERE t.b_id=29
GROUP BY m.t_iddas kann in der form schwierigkeiten ergeben, da du nicht nach der spalte t.date groupiert hast, sondenr nur nach m.t_id. alle spalten, die ausgegeben werden solen und nicht aggregat-funktion sind, müssen in die group by klausel, es sein denn die werte sind alle gleich.
Ilja
-
Hi Ilja
müssen in die group by klausel, es sein denn die werte sind alle gleich.
Ne, ganz prinzipiell immer. Nur wenn sie immer gleich sind, kann man sie ja auch problemlos ins Group By aufnehmen. Wenn nicht, ist schonmal logisch etwas im argen.
Gruss Saniela
-
yo,
Ne, ganz prinzipiell immer. Nur wenn sie immer gleich sind, kann man sie ja auch problemlos ins Group By aufnehmen. Wenn nicht, ist schonmal logisch etwas im argen.
das simmt zumindestens nicht unter mysql, auch wenn ich selbst nicht gerade ein freund davon bin. der sinn, es nicht in das group by aufzunehmen, ist, sich eine zusätzliche sortierung zu sparen, sprich ein performancegewinn. es verwirrt aber dermaßen, dass das negative das positive überwiegt.
Ilja
-
Hi Ilja
das simmt zumindestens nicht unter mysql, auch wenn ich selbst nicht gerade ein freund davon bin.
Ich weiss, aber imho ist dieses Verhalten also Bug anzusehen. Den Sort könnte sich das DBMS auch so sparen sobald es bemerkt, das bei einem Group By sämtliche Primärschlüssel aufgeführt sind.
Gruss Daniela
-
yo,
Ich weiss, aber imho ist dieses Verhalten also Bug anzusehen. Den Sort könnte sich das DBMS auch so sparen sobald es bemerkt, das bei einem Group By sämtliche Primärschlüssel aufgeführt sind.
ob die macher von mysql das als einen bug bezeichen würden, lass ich mal dahingestellt. aber nicht jede spalte ist primärschlüssel oder teil davon. auch unterschieden sich primärschlüssel in aller regel in ihren spaltenwerten. hier geht es um spalten, die alle den gleichen wert besitzen.
Ilja
-
Hi Ilja
ob die macher von mysql das als einen bug bezeichen würden, lass ich mal dahingestellt. aber nicht jede spalte ist primärschlüssel oder teil davon. auch unterschieden sich primärschlüssel in aller regel in ihren spaltenwerten. hier geht es um spalten, die alle den gleichen wert besitzen.
Ja, das ist ja wohl immer gegeben wenn sämtliche Primärschlüssel im Group drin sind...
Gruss Daniela
-
yo,
Ja, das ist ja wohl immer gegeben wenn sämtliche Primärschlüssel im Group drin sind...
wie gesagt, nicht jeder group by beinhaltet auch primärschlüssel. insofern gilt schon meine aussage mit den gleichen spaltenwerten, zumindestens unter mysql. ich würde dir aber zustimmen, dass man diese funktion unter mysql besser wieder entfernt.
Ilja
-
-
-
-
-
-
Hallo Ilja
yo,
SELECT ... DATE_FORMAT(t.date,"%d.%m.%Y %H:%i") as date_f
FROM FORUM_thread t LEFT JOIN FORUM_msg m ON t.t_id = m.t_id
WHERE t.b_id=29
GROUP BY m.t_iddas kann in der form schwierigkeiten ergeben, da du nicht nach der spalte t.date groupiert hast, sondenr nur nach m.t_id. alle spalten, die ausgegeben werden solen und nicht aggregat-funktion sind, müssen in die group by klausel, es sein denn die werte sind alle gleich.
Ich weiß, das mas liest man immer wieder wenn man nach dem Problem sucht.
Es gibt offensichtlich nur einen sauberen weg mit einem subselect bzw. mit einer temporären Tabelle.Oder reicht es wenn auch nach t.date gruppiere?
Es scheint zumindest zu gehen.Struppi.
-
Hi Struppi
Oder reicht es wenn auch nach t.date gruppiere?
Es scheint zumindest zu gehen.Nein, wenn dann nach date_f. Ob das geht, musst du wissen, das hängt von deinen Daten ab. Schlimmstenfalls kriegst du dann pro unterschiedlichem date_f einen Datensatz.
Gruss Daniela
-
-
-
-
-
hi Struppi,
Ich erkläre den OUTER JOIN bei einer 1:1 Beziehung ;-)
Also mal angenommen, Du hast eine Tabelle (names) mit nr, vname und name wobei nr der Primary Key ist.
Nun möchtest Du eine weitere Spalte haben mit dem Alter (age), diese Spalte jedoch in einer anderen Tabelle (ages).
Die referentielle Integrität ist als 1:1 über den Key nr gesichert.
Nun machen wir mal eine Abfrage, die Vorname, Name und Alter ausgibt:
SELECT names.vname, names.name, ages.age
FROM names
LEFT OUTER JOIN ages ON names.nr = ages.nrErgo: Ein LEFT OUTER JOIN verbindet 2 Tabellen so als hättest Du nur eine Tabelle.
Bei 1:n Beziehungen wirds ein bischen komplizierter, aber wenn Du es mit 1:1 verstanden hast, ists am Ende doch nicht so kompliziert.
Viele Grüße, Rolf