CronJob-Service
bei SELFPHP mit ...
|
+ minütlichen Aufrufen
+ eigenem Crontab Eintrag
+ unbegrenzten CronJobs
+ Statistiken
+ Beispielaufrufen
+ Control-Bereich
Führen Sie mit den CronJobs von
SELFPHP zeitgesteuert Programme
auf Ihrem Server
aus. Weitere Infos
|
:: Anbieterverzeichnis ::
Globale Branchen
Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP
:: Newsletter ::
Abonnieren Sie hier den kostenlosen
SELFPHP Newsletter!
|
MySQLi/PDO/(MySQL) Anfänger, Fortgeschrittene oder Experten können hier Fragen und Probleme rund um MySQLi/PDO/(MySQL) diskutieren |
12.10.2010, 09:25:32
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
Left Join auf Select
Einen wunderschönen Guten Tag,
ich habe ein kleines Geschwindigkeitsproblem mit meinem Select, vielleicht könnt ihr mir ja weiterhelfen.
Ich schilder erstmal die Situation:
Ich habe 3 tabellen,
1. "tblWohnung" mit ca. 7000 Rows (Informationen zu Wohnungen)
2. "tblAdresse" mit ca. 8000 Rows (Informationen zu Mietern)
3. "tblMieterZuordnung" mit ca. 9000 Rows (Zuordnung der Mieter zu den Wohnungen)
Nun möchte ich eine Liste Aller Wohnungen welche derzeit Leerstehen, da sind immer ca. 300.
Das problem ist das ich in dieser Liste zusätzlich Informationen zum Vormieter brauche(d.h. der Mieter der zuletzt in der Wohnung gewohnt hat) und Informationen zum Nachmieter (d.h. der Mieter der bald in diese Wohnung einziehen wird, fals vorhanden).
Die ID´s beider stehen somit in der Tabelle "tblMieterZuordnung".
Hier mal ein Pseudocode wie ich es derzeit gelöst habe:
Code:
Code:
SELECT V.name,V.tel,N.name,N.tel
FROM (((SELECT FÜR ALLE LEERSTEHENDEN WOHNUNGEN) LEFT JOIN InformationenZuDenWohnungen)
LEFT JOIN InformationenZuDenMietern...
LEFT JOIN SelectVormieter AS V
LEFT JOIN SelectNachmieter AS N
Das dauert jedoch viel zu lange, da er bei den letzten beiden Selects immer wieder alle 7000 Rows der Tabelle "tblWohnung" durchgeht, und nicht nur die 300 Leeren.
Wie kann ich es realisieren das alle SubSelects nur über diese 300 Wohnungen des ersten Selects laufen?
Hier noch ein paar Allgemeine Informationen zu den Tabellen:
tblWohnung
Primary Key´s : Ve,We
und Sonstige Informationen zur Wohnung:Ort,größe,kaltmiete,ect.
tblAdresse
Primary Key: idAdresse
und Sonstige Informationen zum Mieter:Wohnort,tel,name,ect.
tblMieterZuordnung
Primary Key´s:Ve,We,Vertragsbeginn
Foreign Key´s:Ve,We,idAdresse
Sonstige Felder: Vertragsende, Kündigungsdatum, ect.
Ein Nachmieter ist jeder der ein Vertragsbeginn hat welches in der Zukunft liegt,
und ein Vormieter ist jeder der ein Vertragsende hat welches in der vergangenheit liegt.
Erklärung Ve und We:
Ve ist das Gebäude, We die Wohneinheit
bsp. eines Hauses mit 4 Wohnungen:
Ve: 1
We: 1
Ve: 1
We: 2
Ve: 1
We: 3
Ve: 1
We: 4
Ich hoffe damit könnt ihr was anfangen;)
Gruß
|
12.10.2010, 10:20:29
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Wesentlich für die Geschwindigkeit einer SQL-Abfrage ist ja, wie schnell MySQL die Daten finden kann. Dazu werden in erster Linie Indices benötigt und verwendet.
Als erstes benötige ich mal einen EXPLAIN der echten SQL-Abfrage. Gut formatiert sieht das dann z.B. so aus:
Code:
mysql> EXPLAIN SELECT * FROM kunde;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| 1 | SIMPLE | kunde | system | NULL | NULL | NULL | NULL | 0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.19 sec)
mysql>
Eventuell sind weitere Informationen nötig. Aber mal eines nach dem anderen.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
12.10.2010, 15:46:25
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Hallo,
ich habe das ganze einfach mal als Screenshot gemacht.
Explain
wie du siehst sind oben die 357 Wohnungen die ich brauche, und ich verstehe nicht warum er weiter unten nochmal komplett alles durchgeht.
Gruß
|
12.10.2010, 16:37:55
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Lieber wäre es mir, wenn der EXPLAIN als reiner formatierter Text (siehe [#] in der Editor-Toolbar) hier dargestellt wird.
Aber zu Deiner Frage:
Der EXPLAIN ist ziemlich komplex.
a) Die 1. Zeile verweist auf eine temporäre Tabelle <derived2> die erst weiter unten mit
b) ID=2 (DERIVED) erzeugt wird und dabei die ca. 357 Sätze gefunden werden
c) Der Zugriff auf die Tabelle "W" ist schlecht, siehe Spalte "Extra" (Using filesort etc.). Hier fehlt ein besserer Index.
d) Ebenso ist der Zugriff auf die Tabelle "MZ" ungünstig, hier fehlt ebenfalls ein passender Index
Damit ich Dir helfen kann, benötige ich mindestens für Tabelle W und MZ..
* SHOW CREATE TABLE tbl; -- liefert engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'; -- liefert die Tabellengrößen
sowie am Besten den kompletten SQL dieser Abfrage und eventuell weitere Informationen..
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
13.10.2010, 09:02:22
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Hier Tabelle Wohnung:
Code:
CREATE TABLE `tblWohnung` (
`idWohnung` int(11) NOT NULL AUTO_INCREMENT,
`ve` int(11) DEFAULT '0',
`we` int(11) DEFAULT '0',
`ort` varchar(50) DEFAULT '--',
`plz` varchar(50) DEFAULT '--',
`Ortst` varchar(50) DEFAULT '--',
`strasse` varchar(50) DEFAULT '--',
`nr` varchar(50) DEFAULT '--',
`wfl` double DEFAULT '0',
`lage` varchar(50) DEFAULT '--',
`zim` int(11) DEFAULT '0',
`del` tinyint(1) DEFAULT '0',
`Altbestand` tinyint(1) DEFAULT '0',
`eigennutzung` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`idWohnung`),
KEY `IndexIDWohnungtblWohnung` (`idWohnung`),
KEY `IndexVEtblWohnung` (`ve`),
KEY `IndexWetblWohnung` (`we`),
KEY `altbestand` (`Altbestand`),
KEY `deltblWohnung` (`del`),
KEY `IndexVeWeTblWohnung` (`ve`,`we`)
) ENGINE=MyISAM AUTO_INCREMENT=4072 DEFAULT CHARSET=utf8
Code:
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
tblWohnung MyISAM 10 Dynamic 3976 551 2192096 281474976710655 321536 0 4072 13.10.2010 08:44:50 13.10.2010 08:44:50 13.10.2010 08:44:50 utf8_general_ci
Und MieterZuordnung:
Code:
CREATE TABLE `tblMieterZuordnung` (
`idZuordnung` int(11) NOT NULL AUTO_INCREMENT,
`idAdresse` int(11) DEFAULT NULL,
`ve` int(11) DEFAULT NULL,
`we` int(11) DEFAULT NULL,
`laufnr` int(11) DEFAULT '0',
`Vertragsbeginn` datetime DEFAULT '1800-01-01 00:00:00',
`Vertragsende` datetime DEFAULT '1800-01-01 00:00:00',
`Kuendigungsdatum` datetime DEFAULT '1800-01-01 00:00:00',
`Kuendigungsgrund` varchar(255) DEFAULT '',
`idKontakt` int(11) DEFAULT '0',
`FreiAb` datetime DEFAULT '1800-01-01 00:00:00',
PRIMARY KEY (`idZuordnung`),
KEY `indexLaufnrtblmieterzurodnung` (`laufnr`),
KEY `indexVertragsbeginnEnde` (`Vertragsbeginn`,`Vertragsende`),
KEY `indexIDAdressetblMieterZuordnung` (`idAdresse`),
KEY `IndexVeWeLaufnrTblMieterZuordnung` (`ve`,`we`,`laufnr`)
) ENGINE=MyISAM AUTO_INCREMENT=14064 DEFAULT CHARSET=utf8
Code:
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
tblMieterZuordnung MyISAM 10 Dynamic 5551 140 780096 281474976710655 520192 0 14064 13.10.2010 08:52:52 13.10.2010 08:52:52 13.10.2010 08:52:52 utf8_general_ci
und schließlich der SQL
Code:
SELECT W.idWohnung
FROM
((((
SELECT W.idWohnung, MZ.idZuordnung,MZ.idAdresse,W.ve,W.we
FROM (tblWohnung W LEFT JOIN tblMieterZuordnung MZ ON W.ve = MZ.ve AND W.we = MZ.we)
WHERE
(MZ.Vertragsbeginn =
(SELECT MAX(temp.Vertragsbeginn)
FROM tblMieterZuordnung temp
WHERE temp.ve = W.ve AND temp.we = W.we)
OR MZ.Vertragsbeginn IS NULL)
AND
((MZ.Vertragsende <> '1800-01-01' AND MZ.Vertragsende < NOW()) OR MZ.Vertragsbeginn IS NULL OR (MZ.Vertragsende = '1800-01-01' AND MZ.Vertragsbeginn > NOW()))
AND W.Altbestand = false
AND W.del = false
GROUP BY W.idWohnung
) WHG INNER JOIN tblWohnung W ON W.idWohnung = WHG.idWohnung)
LEFT JOIN tblMieterZuordnung MZ ON MZ.idZuordnung = WHG.idZuordnung)
LEFT JOIN tblAdresse A ON A.idAdresse = WHG.idAdresse)
LEFT JOIN (SELECT *
FROM tblMieterZuordnung tempMZ
WHERE tempMZ.vertragsende =
(SELECT MAX(temp.Vertragsende)
FROM tblMieterZuordnung temp
WHERE temp.ve = tempMZ.ve AND temp.we = tempMZ.we)) AS Vormieter ON Vormieter.ve = W.ve AND Vormieter.we = W.we
noch macht er nicht viel, aber ich wollte bevor ich wirklich damit anfange erstmal schauen das die geschwindigkeit stimmt.
Danke für die hilfe:)
Gruß
|
13.10.2010, 11:51:50
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Zuerst mal ein paar kleine Vorarbeiten für die Tabelle tblWohnung.
a) Auf der Spalte `idWohnung` sind zwei identische Index ( Primary Key und IndexIDWohnungtblWohnung) der zweite kann entfernt werden, da auf dem PK bereits ein Index ist.
b) Die einzelnen Index `IndexVEtblWohnung` (`ve`), `IndexWetblWohnung` (`we`)
können entfert werden und in einen gemeinsamen kombinierten IndexWeVeTblWohnung (we,ve) zusammengefaßt werden. Der hilft auch bei der Suche nach 'we'. Zudem gibt es bereits einen Index auf `IndexVeWeTblWohnung` (`ve`,`we`). Der funktioniert auch bei der Suche nach 've'. Damit werden drei Indices gespart und ein neuer angelegt.
Code:
ALTER TABLE tblWohnung
DROP INDEX IndexIDWohnungtblWohnung,
DROP INDEX IndexVEtblWohnung,
DROP INDEX IndexWetblWohnung,
ADD INDEX IndexWeVeTblWohnung (we,ve);
Könntest Du den EXPLAIN jetzt noch mal ausführen oder mir noch die rechtlichen Tabellendefinitionen plus ein paar Testdaten geben, dann kann ich selbst den EXPLAIN ausprobieren.
Ich schaue mir inzwischen den SQL genauer an..
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
13.10.2010, 12:05:21
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Teil 2)
Weiterhin würde ich in der Tabelle tblWohnung auf diese Defaultwert '1800-...' verzichten und als Default konsequent NULL benutzen. Wie Du in dem Query auch schon teilweise abfrägst. Damit steht entweder etwas drin (ein gültiger Wert) oder die Spalte kann mit "IS NULL" oder "IS NOT NULL" abgefragt werden.
Code:
...
`Vertragsbeginn` datetime DEFAULT '1800-01-01 00:00:00',
`Vertragsende` datetime DEFAULT '1800-01-01 00:00:00',
`Kuendigungsdatum` datetime DEFAULT '1800-01-01 00:00:00',
...
Soweit mal zur ersten Runde.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
14.10.2010, 08:40:29
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Zitat:
Zudem gibt es bereits einen Index auf `IndexVeWeTblWohnung` (`ve`,`we`). Der funktioniert auch bei der Suche nach 've'.
|
Ok, das war mir neu :)
Hier der neue Explain:
Code:
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 357 | |
| 1 | PRIMARY | W | eq_ref | PRIMARY | PRIMARY | 4 | WHG.idWohnung | 1 | |
| 1 | PRIMARY | MZ | eq_ref | PRIMARY | PRIMARY | 4 | WHG.idZuordnung | 1 | Using index |
| 1 | PRIMARY | A | eq_ref | PRIMARY,indexIdAdresse,indexidAdressetblAdresse | PRIMARY | 4 | WHG.idAdresse | 1 | Using index |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 3788 | |
| 4 | DERIVED | tempMZ | ALL | NULL | NULL | NULL | NULL | 5551 | Using where |
| 5 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeLaufnrTblMieterZuordnung | IndexVeWeLaufnrTblMieterZuordnung | 10 | wvs_wws_beta.tempMZ.ve,wvs_wws_beta.tempMZ.we | 1 | Using where |
| 2 | DERIVED | W | ref | altbestand,deltblWohnung | altbestand | 2 | | 3773 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | MZ | ref | IndexVeWeLaufnrTblMieterZuordnung | IndexVeWeLaufnrTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeLaufnrTblMieterZuordnung | IndexVeWeLaufnrTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where |
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+----------------------------------------------+
Zu dem '1800-01-01' Default Wert: Das ist leider ein Relikt aus Urzeiten, und kann ich nicht ohne weiteres ändern da ich dann die komplette software umschreiben muss, was ich aber demnächst auch angehen werden.
Gruß
|
14.10.2010, 11:45:59
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Okay, so läßt sich der EXPLAIN gut lesen.
Die Geschichte mit dem "1800-.." solltest Du auf jeden Fall angehen, dann werden die SQL-Abfragen auch ein Stück einfacher. Gleiches gilt natürlich auch für die DEFAULT '--' Spalten.
So, jetzt zwei weitere Tipp zur SQL-Performance:
a) Der EXPLAIN nutzt den Index altbestand
, der eigentlich nicht sehr aussagekräftig (Selectivität) ist.
Deshalb schlage ich mal folgenden Änderung und einen neuen combinierten Index vor.
Code:
ALTER TABLE tblWohnung
DROP INDEX altbestand,
DROP INDEX deltblWohnung,
ADD INDEX IndexIdAltDelTblWohnung (idwohnung,Altbestand,del);
b) EXPLAIN "..Using filesort;.." eliminieren
Schreibe hinter den GROUP BY in dem SUB-SELECT mal ein ORDER BY NULL. Dann sollte aus dem EXPLAIN der "..Using filesort;" verschwinden.
Code:
...
AND w.altbestand = false
AND w.del = false
GROUP BY w.idwohnung
ORDER BY NULL
) whg
INNER JOIN tblwohnung w
...
EDIT
Anschließend noch ein bisschen Tabellenpflege...
Code:
mysql> OPTIMIZE TABLE tblWohnung;
mysql> ANALYZE TABLE tblWohnung;
So jetzt bitte wieder den aktuellen EXPLAIN zeigen..
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
Geändert von thomas_w (14.10.2010 um 12:00:46 Uhr)
Grund: EDIT ergänzt
|
14.10.2010, 12:15:11
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Das mit dem Filesort hat funktioniert.
Hier mal der neue Explain:
Code:
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 357 | |
| 1 | PRIMARY | W | eq_ref | PRIMARY,IndexIdAltDelTblWohnung | PRIMARY | 4 | WHG.idWohnung | 1 | |
| 1 | PRIMARY | MZ | eq_ref | PRIMARY | PRIMARY | 4 | WHG.idZuordnung | 1 | Using index |
| 1 | PRIMARY | A | eq_ref | PRIMARY,indexIdAdresse,indexidAdressetblAdresse | PRIMARY | 4 | WHG.idAdresse | 1 | Using index |
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 3788 | |
| 4 | DERIVED | tempMZ | ALL | NULL | NULL | NULL | NULL | 5551 | Using where |
| 5 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeLaufnrTblMieterZuordnung | IndexVeWeLaufnrTblMieterZuordnung | 10 | wvs_wws_beta.tempMZ.ve,wvs_wws_beta.tempMZ.we | 1 | Using where |
| 2 | DERIVED | W | ALL | NULL | NULL | NULL | NULL | 3976 | Using where; Using temporary |
| 2 | DERIVED | MZ | ref | IndexVeWeLaufnrTblMieterZuordnung | IndexVeWeLaufnrTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeLaufnrTblMieterZuordnung | IndexVeWeLaufnrTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where |
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+------------------------------+
Optimize Table:
Code:
Table Op Msg_type Msg_text
wvs_wws_beta.tblWohnung optimize status Table is already up to date
Analyze Table:
Code:
Table Op Msg_type Msg_text
wvs_wws_beta.tblWohnung analyze status Table is already up to date
|
Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Gäste: 1)
|
|
Forumregeln
|
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge hochzuladen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.
HTML-Code ist aus.
|
|
|
Alle Zeitangaben in WEZ +2. Es ist jetzt 17:53:35 Uhr.
|