joins von 2 seiten

Hallo,

Kann mir jemand einen Denkanstoss geben, zu folgenden Zenaria auf einem
Mysql 5.0 server

Es gibt 3 Tabellen
ort, firma, kw

wie kann ich jetzt am effizientesten, alle firmen mit ort münchen und kw
bäcker finden.

Den so wie ich es mom gemacht haben, geht das dbms alle firmen durch und
guckt wo beides passt.

Aber es währe doch besser, wenn er 2 tmp tabellen erstellen würde, mit
den Funden den der beiden äusseren Tabellen und nur die Firmen anzeigt,
die in beiden vorkommen.

Ich hoffe das ist halbwegs zu verstehen.

mfg heiko

Re: joins von 2 seiten

Heiko (GreenRover) Henning schrieb:

> Kann mir jemand einen Denkanstoss geben, zu folgenden Zenaria auf einem
> Mysql 5.0 server

Was meinst Du mit "Zenaria"? Szenarien?

> Es gibt 3 Tabellen
> ort, firma, kw
>
> wie kann ich jetzt am effizientesten, alle firmen mit ort münchen und kw
> bäcker finden.

Also sind ort, firma und kw keine Tabellen, sondern Spalten in _einer_
Tabelle. Richtig?

> Den so wie ich es mom gemacht haben, geht das dbms alle firmen durch und
> guckt wo beides passt.

Wie hast Du es denn "mom" (was immer das heißen mag) gemacht? Woher
weißt Du, dass das DBMS alle Firmen durchgeht? Hast Du das mit EXPLAIN
festgestellt? Wie ist Deine Tabelle genau definiert? Hast Du Indizes
gesetzt? Welche?

> Aber es währe doch besser, wenn er 2 tmp tabellen erstellen würde, mit
> den Funden den der beiden äusseren Tabellen und nur die Firmen anzeigt,
> die in beiden vorkommen.

Den Satz bitte noch einmal in _verständlichem_ Deutsch.

> Ich hoffe das ist halbwegs zu verstehen.

Deine Angaben sind zu vage, teilweise widersprüchlich und bisweilen
unverständlich.

Gruß. Claus

Re: joins von 2 seiten

Heiko (GreenRover) Henning schrieb:
>
> Kann mir jemand einen Denkanstoss geben, zu folgenden Zenaria auf einem

Es heißt Szenarien.

> Es gibt 3 Tabellen
> ort, firma, kw
>
> wie kann ich jetzt am effizientesten, alle firmen mit ort münchen und kw
> bäcker finden.

Keine Ahnung. Du lieferst ja keinerlei Informationen über den Aufbau der
Tabellen. Poste mal die Ausgaben von
DESC ;

Die Kristallkugel hat zwar bei dem Versuch, die korrekten Definitionen
auszuspucken den Geist aufgegeben und kann jetzt wiedermal in die
Reparatur, aber ich würde in etwa sowas vermuten:

SELECT wasauchimmer
FROM firma
INNER JOIN ort ON ort.id = firma.ortid
INNER JOIN kw ON kw.id = firma.kwid
WHERE ort.name = 'münchen'
AND kw.name = 'bäcker';

> Den so wie ich es mom gemacht haben, geht das dbms alle firmen durch und
> guckt wo beides passt.

Was soll es denn sonst machen? Und vor allem: Was hast du denn gemacht?
Ein paar mehr Infos und etwas weniger Prosa wären sehr hilfreich.

> Aber es währe doch besser, wenn er 2 tmp tabellen erstellen würde, mit
> den Funden den der beiden äusseren Tabellen und nur die Firmen anzeigt,
> die in beiden vorkommen.

Klar, der Overhead zur Erstellung von zwei Tabellen, darin zu joinen
unde eine Tabelle daraus zu erstellen ist sicherlich deutlich geringer,
als zu joinen und daraus eine Tabelle zu erstellen...

> Ich hoffe das ist halbwegs zu verstehen.

Wenn ich ehrlich bin: Nicht mal im Ansatz. Und das liegt nicht nur an
der mangelhaften Rechtschreibung.

Grüße,
Dominik
--
Wo kämen wir denn hin, wenn jeder sagen würde wo kämen wir hin, aber
niemand gehen würde um zu sehen, wohin wir kämen, wenn wir gingen?
(Autor unbekannt)

Re: joins von 2 seiten

..oO(Heiko (GreenRover) Henning)

>Kann mir jemand einen Denkanstoss geben, zu folgenden Zenaria auf einem
>Mysql 5.0 server

Manchmal ist es zum Szähne ausbeißen ... SCNR

>Es gibt 3 Tabellen
>ort, firma, kw
>
>wie kann ich jetzt am effizientesten, alle firmen mit ort münchen und kw
> bäcker finden.
>
>Den so wie ich es mom gemacht haben, geht das dbms alle firmen durch und
>guckt wo beides passt.

Wie machst Du es denn bisher und was sagt EXPLAIN? Mit vernünftiger
Tabellenstruktur und Indizierung sind das Peanuts.

Was ist eigentlich mit Firmen, die an mehreren Standorten vertreten
sind? Oder kommt das bei Dir nicht vor?

>Aber es währe doch besser, wenn er 2 tmp tabellen erstellen würde, mit
>den Funden den der beiden äusseren Tabellen und nur die Firmen anzeigt,
>die in beiden vorkommen.

Du solltest nicht die Arbeit des DBMS übernehmen. Dessen Optimizer weiß
i.d.R. besser Bescheid, wie eine Anfrage am effizientesten zu bearbeiten
ist.

Micha

Re: joins von 2 seiten

Claus Reibenstein schrieb:
> Deine Angaben sind zu vage, teilweise widersprüchlich und bisweilen
> unverständlich.

OK, dann auf ein neues.

3 Tabellen

CREATE TABLE `firma` (
`id` int(8) unsigned NOT NULL auto_increment,
`titel` varchar(60) NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT AUTO_INCREMENT=116419;

CREATE TABLE `kws` (
`firma` int(8) unsigned,
`kw` varchar(60) NOT NULL default '',
UNIQUE KEY `firma` (`firma`,`kw`),
KEY `kw` (`kw`)
) ENGINE=MyISAM;

CREATE TABLE `orte_firma` (
`ort` int(5) unsigned,
`firma` int(8) unsigned,
UNIQUE KEY `firma` (`firma`,`ort`),
KEY `ort` (`ort`)
) ENGINE=MyISAM;


Die Abfrage

SELECT f.`id`, f.`titel` FROM `firma`f LEFT JOIN `kws` k ON
(f.`id`=k.`firma`) LEFT JOIN `orte_firma` o ON (f.`id`=o.`firma`) WHERE
o.`ort`=10 AND k.`kw`="Bäcker" GROUP BY f.`id`


Und die Vorgehensweise, habe mir mittels explain abgeleitet (es wurden
für den join, die komplette länge von firma angegeben).

Re: joins von 2 seiten

Heiko (GreenRover) Henning schrieb:
>
> CREATE TABLE `firma` (
> `id` int(8) unsigned NOT NULL auto_increment,
> `titel` varchar(60) NOT NULL default '',
> PRIMARY KEY (`id`)
> ) ENGINE=MyISAM DEFAULT AUTO_INCREMENT=116419;
>
> CREATE TABLE `kws` (
> `firma` int(8) unsigned,
> `kw` varchar(60) NOT NULL default '',
> UNIQUE KEY `firma` (`firma`,`kw`),
> KEY `kw` (`kw`)
> ) ENGINE=MyISAM;
>
> CREATE TABLE `orte_firma` (
> `ort` int(5) unsigned,
> `firma` int(8) unsigned,
> UNIQUE KEY `firma` (`firma`,`ort`),
> KEY `ort` (`ort`)
> ) ENGINE=MyISAM;
>
> SELECT f.`id`, f.`titel` FROM `firma`f LEFT JOIN `kws` k ON
> (f.`id`=k.`firma`) LEFT JOIN `orte_firma` o ON (f.`id`=o.`firma`) WHERE
> o.`ort`=10 AND k.`kw`="Bäcker" GROUP BY f.`id`

OK, da stolpere ich zunächst mal über zwei LEFT JOINs, die m.E. nicht zu
deiner Frage nach allen Firmen, die Münchner Bäckereien sind, passen.
Sinnvoller wäre hier normale INNER JOINs (selbe Syntax, nur das LEFT
gegen INNER getauscht).

Als nächstes wundert mich dann, warum du nach Ort Nummer 10 suchst.
Wolltest du nicht nach München suchen? Oder gibt es da evtl. eine
weitere Tabelle mit den Ortsnamen? Dann müßte die natürlich auch noch in
den Join mit aufgenommen werden. Immer vorausgesetzt, es geht noch um
die ursprüngliche Frage.

> Und die Vorgehensweise, habe mir mittels explain abgeleitet (es wurden
> für den join, die komplette länge von firma angegeben).

Den Kram in der Klammer verstehe ich nicht.

Grüße,
Dominik
--
Wo kämen wir denn hin, wenn jeder sagen würde wo kämen wir hin, aber
niemand gehen würde um zu sehen, wohin wir kämen, wenn wir gingen?
(Autor unbekannt)

Re: joins von 2 seiten

Noch ein kleiner Nachtrag, für den aktuellen Lösungsansatz:

SELECT f.`id`, f.`titel`
FROM `orte_firma` o
LEFT JOIN `kws` k ON (k.`firma`=o.`firma`)
INNER JOIN `firma` f ON (k.`firma`=f.`id`)
WHERE k.`kw`="Bäcker" AND o.`ort`=10
GROUP BY f.`id`
ORDER BY f.`titel` ASC
LIMIT 0, 50



Hier das Explain dazu:

+----+-------------+-------+--------+---------------+------- --+---------+---------------------------+------+------------ ---------------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+-------+--------+---------------+------- --+---------+---------------------------+------+------------ ---------------------+
| 1 | SIMPLE | k | ref | firma,kw | kw | 4
| const | 17 | Using temporary; Using filesort |
| 1 | SIMPLE | o | eq_ref | PRIMARY | PRIMARY | 8
| const,k.firma | 1 | Using where; Using index |
| 1 | SIMPLE | f | eq_ref | PRIMARY | PRIMARY | 4
| o.firma | 1 | Using where |
+----+-------------+-------+--------+---------------+------- --+---------+---------------------------+------+------------ ---------------------+

Re: joins von 2 seiten

Dominik Echterbruch schrieb:
> OK, da stolpere ich zunächst mal über zwei LEFT JOINs, die m.E. nicht
> zu deiner Frage nach allen Firmen, die Münchner Bäckereien sind,
> passen.
> Sinnvoller wäre hier normale INNER JOINs (selbe Syntax, nur das LEFT
> gegen INNER getauscht).
Könnte man dadurch nicht auch das GROUB BY auch weglassen.
Dieses dient ja nur dazu, doppelte Funde zu filtern.

> Als nächstes wundert mich dann, warum du nach Ort Nummer 10 suchst.
> Wolltest du nicht nach München suchen? Oder gibt es da evtl. eine
> weitere Tabelle mit den Ortsnamen? Dann müßte die natürlich auch noch in
> den Join mit aufgenommen werden. Immer vorausgesetzt, es geht noch um
> die ursprüngliche Frage.
Ja es geht noch um die alte Frage.
Habe vergessen die 10, für das Beispiel, gegen München aus zu tauschen.
In der reellen Datenbank, wir der Zweig des Ortes über eine ganze reihe
weitere JOIN`s bestimmt.
Diese habe ich hier mal weg gekürzt, um es übersichtlicher zu halten.



> Den Kram in der Klammer verstehe ich nicht.
Also im aller ersten Lösungsansatz, hat EXPLAIN unter `rows` die Anzahl
der Einträge in der Tabelle Firmen angezeigt.



MFG Heiko

Re: joins von 2 seiten

Heiko (GreenRover) Henning meinte:

> Könnte man dadurch nicht auch das GROUB BY auch weglassen.
> Dieses dient ja nur dazu, doppelte Funde zu filtern.

Diese Group by ist wohl eh für A&F. Du brauchst INNER JOINS und
allenfalls DISTINCT.

Gruß, Gregor


--
http://www.gregorkofler.at ::: Landschafts- und Reisefotografie
http://www.image2d.com ::: Bildagentur für den alpinen Raum

Re: joins von 2 seiten

Heiko (GreenRover) Henning schrieb:
> Noch ein kleiner Nachtrag, für den aktuellen Lösungsansatz:
>
> SELECT f.`id`, f.`titel`
> FROM `orte_firma` o
> LEFT JOIN `kws` k ON (k.`firma`=o.`firma`)
> INNER JOIN `firma` f ON (k.`firma`=f.`id`)
> WHERE k.`kw`="Bäcker" AND o.`ort`=10
> GROUP BY f.`id`
> ORDER BY f.`titel` ASC
> LIMIT 0, 50

OK, einfach damit wir mal zu einem Ende kommen hier das Statement, das
ich absetzen würde:

SELECT f.id, f.titel
FROM orte_firma o
INNER JOIN kws k ON k.firma = o.firma
INNER JOIN firma f ON k.firma = f.id
WHERE k.kw = "Bäcker"
AND o.ort = 10
ORDER BY f.titel
LIMIT 50

Liefert die Abfrage das, was du haben möchtest? Was sagt Explain dazu?

Grüße,
Dominik
--
Wo kämen wir denn hin, wenn jeder sagen würde wo kämen wir hin, aber
niemand gehen würde um zu sehen, wohin wir kämen, wenn wir gingen?
(Autor unbekannt)

Re: joins von 2 seiten

Heiko (GreenRover) Henning schrieb:
>
> > OK, da stolpere ich zunächst mal über zwei LEFT JOINs, die m.E. nicht
> > zu deiner Frage nach allen Firmen, die Münchner Bäckereien sind,
> > passen.
> > Sinnvoller wäre hier normale INNER JOINs (selbe Syntax, nur das LEFT
> > gegen INNER getauscht).
>
> Könnte man dadurch nicht auch das GROUB BY auch weglassen.
> Dieses dient ja nur dazu, doppelte Funde zu filtern.

Selbstverständlich. Das Teil hatte ich nur völlig übersehen, sonst hätte
ich dazu auch noch was gesagt.

>> Den Kram in der Klammer verstehe ich nicht.
>
> Also im aller ersten Lösungsansatz, hat EXPLAIN unter `rows` die Anzahl
> der Einträge in der Tabelle Firmen angezeigt.

Klar, du hast ja mit dem LEFT JOIN auch explizit gesagt, daß du alle
Firmen haben möchtest. Google dir mal ein bißchen was zu JOINs zusammen.
Es gibt da mit Sicherheit einiges an deutschsprachigen Tutorials bzw.
Dokumentationen, die die Unterschiede der verschiedenen JOIN Varianten
erläutern.

Grüße,
Dominik
--
Wo kämen wir denn hin, wenn jeder sagen würde wo kämen wir hin, aber
niemand gehen würde um zu sehen, wohin wir kämen, wenn wir gingen?
(Autor unbekannt)

Re: joins von 2 seiten

Dominik Echterbruch schrieb:
> SELECT f.id, f.titel
> FROM orte_firma o
> INNER JOIN kws k ON k.firma = o.firma
> INNER JOIN firma f ON k.firma = f.id
> WHERE k.kw = "Bäcker"
> AND o.ort = 10
> ORDER BY f.titel
> LIMIT 50
>
> Liefert die Abfrage das, was du haben möchtest? Was sagt Explain dazu?

Ja, Sie liefert das gewünschte Ergebnisse.
Es wird hier aber auch , beim explain ausgeben:

select_type: SIMPLE
tabele: orte_firma
type: ref
possible_keys: firma,ort
key: ort
key_len: 4
ref: const
rows: 496
Extra: Using temporary; Using filesort

aber der große Unterschied, die query time:
meine abfrage: 0.0255sec
deinee abfrage: 0.001sec

Also danke ich dir für die Hilfe.
Mfg Heiko