SELECT - JOIN : riesen Performance Problem

S

socke999

Erfahrenes Mitglied
Hallo, ich habe ein kleines Programm das ich in Java geschriben habe und eine MySQL (InnoDB Engine) benutzt. Der MYSQL Server läuft nur local auf meinem normalen PC.

Es ist so ein kleines Programm zum verwalten von Kunden Daten.
Naja, jedenfalls ist es so:

  • Tabelle kunde: PrimaryKey "kid" (AUTO_INCREMENT). Ein Kunde hat diverse attribute wie vorname, nachname, email usw.
  • Tabelle geschaft: PrimaryKey "gid" (AUTO_INCREMENT). Es gibt mehrer Geschäfte. Ein Kunde wird einen Geschäft zugeordnet mit der Spalte geschagt_gid (Foreign Key) in der Tabelle kunde
  • Tabelle kundenstatus: PrimaryKey "kusid" (AUTO_INCREMENT). jeder Kunde kann einen bestimmten Status haben, zum beispiel "gesperrt" usw. Dazu existiert eine Spalte (Foreign Key) kundenstatus_kusid in der Tabelle Kunde
  • Tabelle kind: PrimaryKey "kiid" (AUTO_INCREMENT). Jeder Kunde kann mehrere Kinder haben. Von den Kindern interessiert vor allem das Geburtsdatum.
    In dieser Tabelle kind existiert eine Spalte (Foreign Key) kunde_kid die auf die Tabelle kunde referenziert.
  • Tabelle karte: PrimaryKey "kartennummer". Jeder Kunde kann mehrere solcher kundenkarten haben. Dazu gibt es eine Spalte (Foreign Key) kund_kid in der Tabelle karte die wiederum auf die Tabelle kunde referenziert.
  • Tabelle kartenstatus: PrimaryKey "kasid "(AUTO_INCREMENT). Jeder Karte hat einen bestimmten Status, z.B. "verloren", "gestohlen", "ok", usw. Dazu gibt es in der Tabelle karte eine Spalte (Foreign Key) kartenstatus_kasid die eben auf diese Tabelle kartenstatus referenziert
  • View oldestkind: Eine View auf die Tabelle kind, die für jeden Kunden das älteste kind breitstellt.
  • View youngestkind: Eine View auf die Tabelle kind, die für jeden Kunden das älteste jüngste Kind breitstellt.

Hm, naja, vielelicht poste ich noch mal für die bessere Verständnis die CREATE Befehle:
Code: 
-- 
-- Tabellenstruktur für Tabelle `geschaft`
-- 

CREATE TABLE `geschaft` (
  `gid` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY  (`gid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;






-- 
-- Tabellenstruktur für Tabelle `kundenstatus`
-- 

CREATE TABLE `kundenstatus` (
  `kusid` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY  (`kusid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;



-- 
-- Tabellenstruktur für Tabelle `kunde`
-- 

CREATE TABLE `kunde` (
  `kid` int(11) NOT NULL auto_increment,
  `anrede` varchar(20) default NULL,
  `vorname` varchar(50) NOT NULL,
  `nachname` varchar(50) NOT NULL,
  `plz` varchar(6) default NULL,
  `geschaft_gid` int(11) default NULL,
  `seit` date default NULL,
  `staat` varchar(30) default NULL,
  `ort` varchar(100) default NULL,
  `hausnummer` varchar(10) default NULL,
  `strasse` varchar(50) default NULL,
  `email` varchar(100) default NULL,
  `handy` varchar(100) default NULL,
  `kundenstatus_kusid` int(11) NOT NULL default '1',
  `kommentar` varchar(250) NOT NULL,
  PRIMARY KEY  (`kid`),
  FOREIGN KEY (geschaft_gid) REFERENCES geschaft(gid),
  FOREIGN KEY (kundenstatus_kusid) REFERENCES kundenstatus(kusid)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4608 ;




-- --------------------------------------------------------



-- 
-- Tabellenstruktur für Tabelle `kartenstatus`
-- 

CREATE TABLE `kartenstatus` (
  `kasid` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY  (`kasid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;




-- 
-- Tabellenstruktur für Tabelle `karte`
-- 

CREATE TABLE `karte` (
  `kartennummer` bigint(20) NOT NULL,
  `kartenstatus_kasid` int(11),
  `kunde_kid` int,
  PRIMARY KEY  (`kartennummer`),
  FOREIGN KEY (kartenstatus_kasid) REFERENCES kartenstatus(kasid) ON DELETE SET NULL
  FOREIGN KEY (kunde_kid) REFERENCES kunde(kid) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



-- 
-- Tabellenstruktur für Tabelle `kind`
-- 

CREATE TABLE `kind` (
  `kiid` int(11) NOT NULL auto_increment,
  `vorname` varchar(30) default NULL,
  `nachname` varchar(30) default NULL,
  `geburtsdatum` date NULL,
  `kunde_kid` int(11) NOT NULL,
  PRIMARY KEY  (`kiid`),
  FOREIGN KEY (kunde_kid) REFERENCES kunde(kid) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6245 ;


-- 
-- Tabellenstruktur für Tabelle `oldestkind`
-- 

CREATE VIEW oldestkind AS select min(kind.geburtsdatum) AS `geburtsdatum`,`kind`.`kunde_kid` AS `kunde_kid` ,`kind`.`kiid` from `kind` group by `kind`.`kunde_kid` order by `kind`.`kunde_kid`;

-- --------------------------------------------------------

-- 
-- Tabellenstruktur für Tabelle `youngestkind`
-- 

CREATE VIEW `youngestkind` AS select max(`kind`.`geburtsdatum`) AS `geburtsdatum`, `kind`.`kunde_kid` AS `kunde_kid` ,`kind`.`kiid` from `kind` group by `kind`.`kunde_kid` order by `kind`.`kunde_kid`;


Naja, das problem ist nun fogendes:
Ich dachte mir, es wäre am besten, wenn ich alle Daten in einer SELECT Anfrage erfassen könnte und diese dann in Java zu "richtigen" Kunden Objecte, Karte Objecte, Kind Objecte usw. "parse". Das "parsen" funktioniert auch ganz einwandfrei und schnell, also weniger als 1 sek.

NUR:
das Abfragen, also das ausführen der SELECT QUERY mit JOINS dauert aber so extrem lange!

Also ich habe Beispieldaten:
2000 Kunden (432 KB)
100 Geschäfte (16 KB)
9000 Karten (600 KB)
8700 Kinder (1,7 MB)
100 KartenStatus (16 KB)
100 KundenStatus (16 KB)

Mit meinem SELECT JOIN dauert die Abfrage ganze 18 Minuten! FAIL!

Meine Abfrage sieht so aus:
Code: 
SELECT kunde.*, kartennummer, kind.kiid, kind.vorname as kind_vorname, kind.nachname as kind_nachname, kind.geburtsdatum, kartenstatus_kasid, youngestkind.kiid as youngestkind_kiid, youngestkind.geburtsdatum as youngestkind_geburtsdatum ,oldestkind.kiid as oldestkind_kiid, oldestkind.geburtsdatum as oldestkind_geburtsdatum
 FROM kunde
LEFT OUTER JOIN karte ON (kunde.kid = karte.kartennummer)
LEFT OUTER JOIN kind ON (kind.kunde_kid = kunde.kid)
LEFT OUTER JOIN kartenstatus ON (karte.kartenstatus_kasid = kartenstatus_kasid)
LEFT OUTER JOIN youngestkind ON (youngestkind.kunde_kid = kunde.kid)
LEFT OUTER JOIN oldestkind ON (oldestkind.kunde_kid = kunde.kid)
GROUP BY kind.kiid,karte.kartennummer
ORDER BY kunde.kid

Naja, also es ist dann so, dass in meiner Ergebnis Zeilen sozusagen "doppelt" sind:
also die "einfachen" Kunden daten wie vorname, nachname usw. (kunde.*) werden mehrfach gelistet, jedoch immer mit unterschiedlichen Kinder bzw. Karten.

Naja, so wächst aber natürlich die Anzahl an Zeilen beträchtlich.

Also, wie könnte man das optimieren?
Also an SELECT SQL wird es sicher eine effizientere Lösung geben, aber um ehrlich zu sein ist mir bis jetzt keine eingefallen. Vielleicht kann man das irgendwie besser mit einer View lösen, aber die View hat ja dann auch nur im Prinzip den selben SELECT Befehl, also gleiche Performance.

Ich habe mir gedacht, ich könnte die Views youngestkind und oldestkind irgendwie zusammenlegen zu einer View, um sich einen JOIN zu sparen, aber macht das wirklich einen größeren unterschied?

Wie funktioniert das eigentlich genau mit INDEXE? Also ich habe ja nur PRIMARY KEYS, die ja aber auch INDEXE sind, soweit ich weis zumindest. Sind FOREIGN KEYS auch INDEXE? Wenn nein, würde es die Performance verbessern wenn ich die FOREIGN KEYS zu INDEXE mache?

Was gibt es sonst noch für Möglichkeiten?
Es wäre super, wenn mir jemand weiter helfen könnte
 
Ich habe gerade gestern einige Tipps geschrieben
http://www.tutorials.de/forum/relat...351737-abfrage-beschleunigen.html#post1822785

Bei dir ist ein Problem, dass du keine Dateneinschränkungen hast und immer alle Daten ausgeben willst.

item: Warum nimst die Karte und den Kartenstatus ins Query? Du brauchst sie nicht. Das einzige was du da ausgeben willst ist die Kartennumer und die ist gleich wie kunde.kid.

item: Den GROUP BY macht keinen Sinn. Du gruppierst auf 2 Felder. Die DB gruppiert automatisch auch auf alle Felder die mit keiner der folgenden Funktionen dieser Seite (http://dev.mysql.com/doc/refman/5.1/de/group-by-functions.html) ausgeführt werden. Ergo bei dir auf alle Felder. Ein GroupBy auf alle Felder bewirkt höchstens, dass due keine doppelten Datensätz anzeigst (inkl. Kinder!). Ein DISTINCT ist da eleganter

item: du greiffst auf 2 Views zu, die weider auf kinder zugreiffen. In diesen hast du ganz komische GROUP BY drin. Ebenfalls hast gu da noch ein ORDER BY drin. Den brauchst du da wirklich nicht.
Wenn ich die Views anschaue - die machen wenig Sinn.

Sowas leifert glaub eher das was du suchst (für youngestkind analog):
SQL: 
CREATE VIEW oldestkind AS 
SELECT 
	kind.kiid,
	kind.kunde_kid,
        kind.geburtsdatum 
FROM
	kind
	JOIN	(	SELECT
					min(geburtsdatum) AS geburtsdatum,
					kunde_kid 
				FROM
					kind
				GROUP BY
					kunde_kid
			) AS mk
		ON kind.geburtsdatum = mk.geburtsdatum AND kind.kunde_kid = mk.kunde_kid;
Ein Index über kind.geburtsdatum und kind.kunde_kid kann nicht schaden
SQL: 
CREATE INDEX idx_kind_gdate ON kind (geburtsdatum ASC, .kunde_kid);

Versuch auch mal einen Index über die folgenden 3 Felder zu legen. ggf. muss er damit nachher nicht auf die Tabelle zugreifen sondern kann direkt mit dem Index arbeiten
SQL: 
CREATE INDEX idx_kind_gdate ON kind (geburtsdatum ASC, kunde_kid, kiid);
Mehr über Indexe gibts hier: http://dev.mysql.com/doc/refman/5.1/de/mysql-indexes.html

Das ergibt dan etwa ein Query in der Art
SQL: 
SELECT DISTINCT
	kunde.*, 
	kunde.kid AS kartennummer, 
	kind.kiid, 
	kind.vorname as kind_vorname, 
	kind.nachname as kind_nachname, 
	kind.geburtsdatum, kartenstatus_kasid, 
	youngestkind.kiid as youngestkind_kiid, 
	youngestkind.geburtsdatum as 
	youngestkind_geburtsdatum ,
	oldestkind.kiid as oldestkind_kiid, 
	oldestkind.geburtsdatum as oldestkind_geburtsdatum
FROM kunde
LEFT JOIN kind ON (kind.kunde_kid = kunde.kid)
LEFT JOIN youngestkind ON (youngestkind.kunde_kid = kunde.kid)
LEFT JOIN oldestkind ON (oldestkind.kunde_kid = kunde.kid)


Naja, also es ist dann so, dass in meiner Ergebnis Zeilen sozusagen "doppelt" sind:
also die "einfachen" Kunden daten wie vorname, nachname usw. (kunde.*) werden mehrfach gelistet, jedoch immer mit unterschiedlichen Kinder bzw. Karten.
Zum Vergrößern anklicken....
Das ist logisch. Pro Kind gibts eine Zeile.
Wie willst du es sonst gelöst haben? Höchstens noch über 2 Queries die du im Java mit einer Schleife durchläufst. Wenn du aber alle Daten haben willst, ist ein Query schneller, da du dann vom Programm nur ein DB-Aufruf hast.
Um dir da weiterzuhelfen müsste man wissen, was du damit machen möchtest
 
Zuletzt bearbeitet von einem Moderator:
Hallo, danke für die antwort,
ja das Problem mit der Dateneinschränkung ist mir bewusst. Es ist aber so, dass ich will dass mir alles angezeigt wird.

Es gibt dann auch eine Suche in meinem Porgramm, wo dann mit der WHERE Clause gearbeitet wird um die Daten zu "filtern"/einzuschränken.

Und das ist auch der Grund wieso ich das ganze nicht gut über:
Höchstens noch über 2 Queries die du im Java mit einer Schleife durchläufst.
Zum Vergrößern anklicken....
Ich könnte natürlich so machen:
1) SELECT * FROM kunde
2) SELECT * FROM karte
3) SELECT * FROM kind

und diese dann in java richtig zusammen basteln lassen, aber wenn ich zum Beispiel nur alle Kunden haben möchte, die ein Kind haben das älter als "1.1.2002" ist, dann ist das schon wieder nicht sehr leicht realisierbar. Deshalb wäre es viel besser alles mit JOIN verbinden.

karte muss ich schon noch dazu einbeziehen: Karte ist sozusagen eine Kundenkarte, da hat jede Karte so eine Nummer (auch in form von Strichcode). diese nummer wird dann in der Spalte kartennummer gespeichert

Also kartennummer ist nicht gleich kunde.kid

Was die Views youngestkind und oldestKind betreffen, dann stimmen die schon so wie ich sie gehabt habe.

Bei deinem
Code: 
CREATE VIEW oldestkind AS
SELECT
    kind.kiid,
    kind.kunde_kid,
        kind.geburtsdatum
FROM
    kind
    JOIN    (   SELECT
                    min(geburtsdatum) AS geburtsdatum,
                    kunde_kid
                FROM
                    kind
                GROUP BY
                    kunde_kid
            ) AS mk
        ON kind.geburtsdatum = mk.geburtsdatum AND kind.kunde_kid = mk.kunde_kid;
Da bekomm ich ja alle Kinder von jeden Kunden (zwar nach min(geburtsdatum) geordnet) und nicht mehr nur das eine Kind zu den Kunden das am jüngsten/ältesten ist.

Diese Views benötige ich eigentlich nur dazu, um zum beispiel bei einer suche zu sagen: Gib mir die Kunden, deren jüngstes Kind jünger als 1.1.2003 ist, zum Beispiel.

Diese Views werde ich auch nur JOINEN, wenn expliziet eine Anfrage mit WHERE Clause jüngstes/ältestes kind ist so und so ...


Also das ganze sollte so in JAVA sein:
Ich mach in Java diese SLECT JOIN anweisung,
und dann bekomme ich eine Liste von Kunden Objecte.
Jedes Kunde Object hat dann intern noch eine Liste mit Karten Objecten und noch eine weitere Liste mit Kinder Objecten.
 
Mir ist nicht genau klar, welche Abfrage-Parameter du hast, und was du eigentlich genau in deiner Liste haben möchtest.
Eventuell kannst du ein paar JOINs durch geschickte Subqueries ersetzen, indem du schreibst
WHERE abc.xyz IN ( SELECT ... )
Es bietet sich an, die Subqueries, wenn machbar, auf Tabellen mit möglichst wenig Datensätzen zu machen, um so eine gute Vorfilterung zu haben.
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück