Re: Joins / Performance

[Martin Ramsch <m.ramsch_(at)_computer.org>]

Andreas Kempf aka 'amalesh' schrieb am Donnerstag, den 19. August 1999:
> >> select distinct(a.ort), count(a.id) as anzahl 
> >>   from person a, 
> >>        rel_niederlassung_person b 
> >>  where a.id != b.person_id 
> >>  group by ort 
> >>  order by ort
[...]
> 
> >Zur SQL-Anfrage selbst:
> >   Das funktioniert wirklich so?  Kann ich nicht recht glauben ...
> >
> >   Wenn ich das richtig verstehe, wird doch so pro Ort und pro Person
> >   dieses Ortes jede andere in der Tabelle "rel_niederlassung_person"
> >   vorkommende Person gezählt!
> 
> Wenn ich "where a.id != b.person.id" benutze? Dann werden IMO alle
> records selektiert, deren id aus tabelle a _nicht_ der person_id der
> tabelle b entspricht. Genau das will ich ja.
> 
> >   Das ist nicht, was Du oben als Ziel angibst!
> 
> Klarer formuliert: Ich will die anzahl aller personen zu jedem ort,
> für alle personen, die nicht einer niederlassung angehören.

Aber genau diese Zahl bekommst Du nicht (oder höchstens durch Zufall)!

Ganz konkretes Beispiel mit Testdaten (siehe Anhang):

    mysql> SELECT * FROM a;       mysql> SELECT * FROM b; 
    +----+----------+             +----+                  
    | id | ort      |             | id |                  
    +----+----------+             +----+                  
    |  1 | passau   |             |  3 |                  
    |  2 | münchen  |             |  4 |                  
    |  3 | salzburg |             +----+            
    |  4 | passau   |             2 rows in set (0.00 sec)                  
    +----+----------+                               
    4 rows in set (0.00 sec)      

In Tabelle a steht also, welche Personen in welchem Ort sind.
In Tabelle b steht, welche Personen einer Niederlassung angehören.

Nun sehen wir mal Schritt für Schritt, was bei "a.id != b.id"
genau passiert.
(Sortierungen mit ORDER BY mache ich jeweils nur der Übersichtlichkeit
halber; an der Ergenismenge selbst ändert sich dadurch ja nichts.)

Erstmal das komplette Kreuzprodukt, das man bekommt, wenn man einen
Join ohne Bedingung durchführt ("p_in_o" steht für "Person in Ort",
"p_in_n" für "Person in Nierderlassung"):

    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a, b
       --> ORDER BY p_in_o, p_in_n;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | passau   |      1 |      3 |
    | passau   |      1 |      4 |
    | münchen  |      2 |      3 |
    | münchen  |      2 |      4 |
    | salzburg |      3 |      3 |    <-- Gleichheit
    | salzburg |      3 |      4 |
    | passau   |      4 |      3 |
    | passau   |      4 |      4 |    <-- Gleichheit
    +----------+--------+--------+  
    8 rows in set (0.00 sec)

Das kombiniert also alle Tupel aus Tabelle a mit allen Tupeln aus
Tabelle b.

Die markierten Zeilen haben die Bedeutung:  diese Person wohnt in
diesem Ort und ist in einer Niederlassung.

Die anderen Zeilen haben die Bedeutung: die Person p_in_o wohnt in
diesem Ort und die andere Person p_in_n ist in einer Niederlassung.
Das ergibt also keine Aussage über _eine_ Person!

Mit dem Zusatz "WHERE a.id != b.id" fallen jetzt die Zeilen weg, wo
"a.id = b.id" wäre:

    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a, b
       --> WHERE a.id != b.id
       --> ORDER BY a.id, b.id;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | passau   |      1 |      3 |
    | passau   |      1 |      4 |
    | münchen  |      2 |      3 |
    | münchen  |      2 |      4 |
    | salzburg |      3 |      4 |
    | passau   |      4 |      3 |
    +----------+--------+--------+
    6 rows in set (0.00 sec)
     
Diese Tabelle enthält in Spalte p_in_o NICHT nur die Leute, die in
keiner Niederlassung sind, siehe die letzten beiden Zeilen!

Und selbst bei den Personen in Spalte p_in_o, die tatsächlich in
keiner Niederlassung sind, stimmen die Anzahlen nicht, die Du per
COUNT(*) ... GROUP BY ... ermittelst, da z.B. Person 1 zweimal 
auftaucht.  Gruppiert nach ort ergeben sich für 'passau' hier
zusammen dann drei Zeilen, richtig wäre aber eine!


Was Du also wirklich suchst, sind die Personen, die nicht in der
Tabelle b auftauchen (die also keiner Nierderlassung angehören.)

Erster Schritt:  Personen, die einer Niederlassung angehören:

    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a, b
       --> WHERE a.id = b.id
       --> ORDER BY p_in_o, p_in_n;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | salzburg |      3 |      3 |
    | passau   |      4 |      4 |
    +----------+--------+--------+
    2 rows in set (0.00 sec)

Das sind also genau die zwei Zeilen, die oben mit "Gleichheit"
markiert sind.

Dummerweise fallen so die anderen Personen aus Tabelle a weg, die
genau uns aber interessieren würden.

GENAU hier hilft der LEFT JOIN!
Denn das bewirkt, daß auch die Werten aus der linken Tabelle
ausgegeben werden, zu denen es keinen Join-Partner aus der anderen
Tabelle gibt:

    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a LEFT JOIN b
       -->      ON a.id = b.id
       --> ORDER BY p_in_o, p_in_n;
    +----------+--------+--------+
    | ort      | p_in_o | p_in_n |
    +----------+--------+--------+
    | passau   |      1 |   NULL |
    | münchen  |      2 |   NULL |
    | salzburg |      3 |      3 |
    | passau   |      4 |      4 |
    +----------+--------+--------+
    4 rows in set (0.00 sec)

Und jetzt ist klar: die Personen, die in keiner Niederlassung sind,
bekommt man mit dem Zusatz:  WHERE b.id IS NULL
(Im WHERE-Teil kann man leider nicht mit Alias-Namen arbeiten.)

    mysql> SELECT a.ort, a.id AS p_in_o, b.id AS p_in_n
       --> FROM a LEFT JOIN b
       -->      ON a.id = b.id
       --> WHERE b.id IS NULL
       --> ORDER BY p_in_o, p_in_n;
    +---------+--------+--------+
    | ort     | p_in_o | p_in_n |
    +---------+--------+--------+
    | passau  |      1 |   NULL |
    | münchen |      2 |   NULL |
    +---------+--------+--------+
    2 rows in set (0.00 sec)


Zum zählen dieser Personen pro Ort kommt dann genau die SQL-Anfrage
heraus, die ich Dir geschrieben hatte. :)
(Das ORDER BY kann dann wieder weg; war ja nur zur Verhübschung.)

> >   Mein Lösungsvorschlag:
> >
> >   SELECT a.ort, COUNT(*) AS anzahl
> >   FROM person AS a LEFT JOIN rel_niederlassung_person AS b
> >        ON a.id = b.id
> >   WHERE b.id IS NULL
> >   GROUP BY a.ort;
> 
> Ist das gleiche ergebnis, nur ein paar 1/100 sek. langsamer als
> "where a.id != b.person_id". Momentan habe ich noch 4000 datensätze
> in der tabelle person, wird aber stark ansteigen (ca. 250.000).
> Daher ist gute performance extrem wichtig.

Lieber ein paar 1/100 sek. langsamer, statt falsch. :)

Ich kann immer noch nicht verstehen, aus welcher komischen
Konstellation heraus Deine und meine SQL-Anfrage das gleiche Ergebnis
liefern konnten ...?

Ciao,
  Martin
-- 
Martin Ramsch <m.ramsch_(at)_computer.org> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
# MySQL dump 5.13
#
# Host: mysql    Database: test
#--------------------------------------------------------
# Server version	3.22.19b

# Manuell eingefügt:
USE test;

#
# Table structure for table 'a'
#
DROP TABLE IF EXISTS a;
CREATE TABLE a (
  id int(11) DEFAULT '0' NOT NULL,
  ort char(10),
  PRIMARY KEY (id)
);

#
# Dumping data for table 'a'
#

LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,'passau'),(2,'münchen'),(3,'salzburg'),(4,'passau');
UNLOCK TABLES;

#
# Table structure for table 'b'
#
DROP TABLE IF EXISTS b;
CREATE TABLE b (
  id int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id)
);

#
# Dumping data for table 'b'
#

LOCK TABLES b WRITE;
INSERT INTO b VALUES (2),(3),(5),(7);
UNLOCK TABLES;