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 |
14.10.2010, 12:31:41
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Schade, der neue Index wird bisher nicht verwendet. Da müssen wir noch etwas weiter rantasten.
Folgendes kann vermutlich an dem SQL noch geändert werden..
a) SELECT * eliminieren
Soweit ich es sehe, werden hier nur die zwei Spalten ve, we benötigt.
Code:
...
LEFT JOIN (SELECT ve, we
FROM tblmieterzuordnung tempmz
...
b) Weiteren umgestellten index
Und noch ein Test-Index, falls dieser nichts bringen, können sie wieder problemlos entfernt werden.
Code:
ALTER TABLE tblWohnung
ADD INDEX IndexAltDelIdTblWohnung (Altbestand,del,idwohnung);
Jetzt wäre der aktuelle EXPLAIN wieder spannend.
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
14.10.2010, 12:51:34
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Der neue Index wird nun verwendet, durch das entfernen des "*" ist der select um 7 sekunden schneller.
Hier mal der aktuelle 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 | ref | IndexAltDelIdTblWohnung | IndexAltDelIdTblWohnung | 4 | | 1945 | Using where |
| 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 |
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+-------------+
|
14.10.2010, 13:08:44
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Sehr gut, dann kann der eine Index der nicht verwendet wird, wieder entfernt werden.
Code:
ALTER TABLE tblWohnung
DROP INDEX IndexIdAltDelTblWohnung;
Als nächstes muss die Tabelle tblMieterZuordnung optimiert werden. Soweit ich sehe kommt MySQL speziell mit der Sub-Tabelle "tempMZ" nicht gut zurecht.
Also bitte mal folgenden Index ausprobieren:
Code:
ALTER TABLE tblMieterZuordnung
DROP INDEX IndexVeWeLaufnrTblMieterZuordnung,
ADD INDEX IndexVeWeVbVeTblMieterZuordnung (`ve`,`we`,`Vertragsbeginn`,`Vertragsende`);
Interessant wäre zudem dies:
Code:
mysql>SHOW VARIABLES LIKE '%buffer%'; -- wichtige MySQL Server Einstellungen
So, nun wie wieder den EXPLAIN erstellen...
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
14.10.2010, 13:51:58
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Okay, der Index wird nun auch benutzt.
Hier der 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 | index | NULL | IndexVeWeVbVeTblMieterZuordnung | 28 | NULL | 5551 | Using where; Using index |
| 5 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeVbVeTblMieterZuordnung | IndexVeWeVbVeTblMieterZuordnung | 10 | wvs_wws_beta.tempMZ.ve,wvs_wws_beta.tempMZ.we | 1 | Using where; Using index |
| 2 | DERIVED | W | ref | IndexAltDelIdTblWohnung | IndexAltDelIdTblWohnung | 4 | | 1945 | Using where |
| 2 | DERIVED | MZ | ref | IndexVeWeVbVeTblMieterZuordnung | IndexVeWeVbVeTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeVbVeTblMieterZuordnung | IndexVeWeVbVeTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where; Using index |
+----+--------------------+------------+--------+-------------------------------------------------+---------------------------------+---------+-----------------------------------------------+------+--------------------------+
und Show Variables
Code:
Variable_name Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
join_buffer_size 131072
key_buffer_size 16777216
myisam_sort_buffer_size 8388608
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 131072
read_rnd_buffer_size 262144
sort_buffer_size 2097144
sql_buffer_result OFF
|
14.10.2010, 17:16:52
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
So weit so gut. Wie ist jetzt eigentlich die Geschwindigkeit im Vergleich zu vorher?
Was man noch tun kann, ist die Abfragen auf NULL entfernen, da diese Spalten aufgrund dem DEFAULT nie NULL sein können.
siehe
Code:
..
OR mz.vertragsbeginn IS NULL
..
Weiteres geht nun ziemlich ins Detail. Mir scheint der SQL ist z.B nicht "sinnvoll" formuliert, aber ohne ausreichende Projektkenntnisse lässt sich das nicht viel machen.
z. Beispiel ist die Tabelle tbladresse eingebunden, wird aber nie verwendet
Code:
..
LEFT JOIN tbladresse a
ON a.idadresse = whg.idadresse)
..
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
18.10.2010, 08:44:59
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Die geschwindkeit ist nun ca. 9 sekunden schneller(0,9).
Aber der Select ist ja so noch nicht fertig, ich werde ihn mal weiter machen und mich dann einfach nochmal melden wenn ich fragen hab.
Vielen Dank nochmal für die hilfe;)
Gruß
|
19.10.2010, 11:03:47
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Ich habe den Select ein bischen weiter gemacht, jedoch stimmt die Geschwindigkeit leide rnoch immer nicht so ganz.
Hier mal der aktuelle Select:
Code:
SELECT Vormieter.name,Vormieter.tel,Vormieter.Kuendigungsdatum,Vormieter.Kuendigungsgrund
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 tblAdresse A ON A.idAdresse = WHG.idAdresse)
LEFT JOIN (SELECT tempMZ.ve,tempMZ.we,CONCAT(tempA.nachname,", ",tempA.vorname) AS Name,tempA.tel,tempMZ.Kuendigungsdatum,tempMZ.Kuendigungsgrund
FROM tblMieterZuordnung tempMZ INNER JOIN tblAdresse tempA ON tempMZ.idAdresse = tempA.idAdresse
WHERE tempMZ.Vertragsende = (SELECT MAX(temp.Vertragsende) FROM tblMieterZuordnung temp WHERE temp.ve = tempMZ.ve AND temp.we = tempMZ.we) AND tempMZ.Vertragsende <> '1800-01-01') Vormieter
ON Vormieter.ve = W.ve AND Vormieter.we = W.we)
Je mehr Spalten ich bei dem "Vormieter" Select hinzufüge, desto langsamer wird der Select. Bei einer Spalte 0,9 Sekunden, bei 2 Spalten 1,1... mittlerweile ist er schon bei 2,2 Sekunden. Kann das an Indizes liegen?
Der Explain ist noch der selbe wie oben:
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 | index | NULL | IndexVeWeVbVeTblMieterZuordnung | 28 | NULL | 5551 | Using where; Using index |
| 5 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeVbVeTblMieterZuordnung | IndexVeWeVbVeTblMieterZuordnung | 10 | wvs_wws_beta.tempMZ.ve,wvs_wws_beta.tempMZ.we | 1 | Using where; Using index |
| 2 | DERIVED | W | ref | IndexAltDelIdTblWohnung | IndexAltDelIdTblWohnung | 4 | | 1945 | Using where |
| 2 | DERIVED | MZ | ref | IndexVeWeVbVeTblMieterZuordnung | IndexVeWeVbVeTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | temp | ref | IndexVeWeVbVeTblMieterZuordnung | IndexVeWeVbVeTblMieterZuordnung | 10 | wvs_wws_beta.W.ve,wvs_wws_beta.W.we | 1 | Using where; Using index |
+----+--------------------+------------+--------+-------------------------------------------------+---------------------------------+---------+-----------------------------------------------+------+--------------------------+
|
19.10.2010, 13:46:48
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Kannst Du mal den SQL umformulieren...
anstatt die Adressfelder des "Vormieter" hier tief im Sub-Select zu lesen
Code:
..
SELECT tempmz.ve,
tempmz.we,
Concat(tempa.nachname, ", ", tempa.vorname) AS name,
tempa.tel,
tempmz.kuendigungsdatum,
tempmz.kuendigungsgrund
FROM tblmieterzuordnung tempmz
INNER JOIN tbladresse tempa
ON tempmz.idadresse = tempa.idadresse
WHERE tempmz.vertragsende = (SELECT Max(TEMP.vertragsende)
FROM tblmieterzuordnung TEMP
WHERE TEMP.ve = tempmz.ve
AND TEMP.we = tempmz.we)
AND tempmz.vertragsende <> '1800-01-01'
..
könnten diese Spalten bereits über den Alias A in der obersten SELECT-Liste direkt angegeben werden. Also anstatt so:
Code:
SELECT vormieter.name,
vormieter.tel,
vormieter.kuendigungsdatum,
vormieter.kuendigungsgrund
...
Geht meiner Meinung nach auch dies:
Code:
SELECT Concat(a.nachname, ", ", a.vorname) AS name,
a.tel,
vormieter.kuendigungsdatum,
vormieter.kuendigungsgrund
...
Wie schau nun der EXPLAIN bzw. die Geschwindigkeit aus.
Zudem brauche ich noch den SHOW CREATE TABLE von der Tabelle tbladresse.
Meiner Meinung nach muss der SQL komplett umgeschrieben werden und wesentliche Abfragen zusammengefaßt werden. Z.B. die Abfrage nach Vertragsbeginn und -ende auf einmal durchführen.
Code:
..
SELECT Max(vertragsbeginn), Max(vertragsende)
FROM tblmieterzuordnung
...
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
19.10.2010, 14:09:54
|
Anfänger
|
|
Registriert seit: Oct 2010
Alter: 34
Beiträge: 15
|
|
AW: Left Join auf Select
Das Problem ist das ich zuzüglich zu dem Namen des Vormieters noch den Namen des Nachmieters brauche.
Hier ich habe es mal erweitert:
Code:
SELECT W.frei,W.InArbeit,W.TechnLeerstand, W.Altbestand, W.ve,W.we, CONCAT(W.strasse," ", W.nr) as Strasse,W.F4,W.zim,W.wfl,W.Lage,W.Baujahr, W.wbs,
Vormieter.name,Vormieter.tel,Vormieter.Kuendigungsdatum,Vormieter.Kuendigungsgrund,Nachmieter.name,Nachmieter.tel
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 (SELECT tempMZ.ve,tempMZ.we,tempMZ.Kuendigungsdatum,tempMZ.Kuendigungsgrund,CONCAT(tempA.nachname,", ",tempA.vorname) AS Name,tempA.tel
FROM tblMieterZuordnung tempMZ INNER JOIN tblAdresse tempA ON tempMZ.idAdresse = tempA.idAdresse
WHERE tempMZ.Vertragsende = (SELECT MAX(temp.Vertragsende) FROM tblMieterZuordnung temp WHERE temp.ve = tempMZ.ve AND temp.we = tempMZ.we) AND tempMZ.Vertragsende <> '1800-01-01') Vormieter
ON Vormieter.ve = W.ve AND Vormieter.we = W.we)
LEFT JOIN (SELECT tempMZ.ve,tempMZ.we,tempMZ.Kuendigungsdatum,tempMZ.Kuendigungsgrund,CONCAT(tempA.nachname,", ",tempA.vorname) AS Name,tempA.tel
FROM tblMieterZuordnung tempMZ INNER JOIN tblAdresse tempA ON tempMZ.idAdresse = tempA.idAdresse
WHERE tempMZ.vertragsbeginn > NOW()) AS Nachmieter ON Nachmieter.ve = W.ve AND Nachmieter.we = W.we
wobei mir nun aufgefallen ist das der Nachmieter-Subquery absolut kein Problem ist.
Der Select ist kein bischen langsamer als ohne ihn.
D.h. das problem liegt in der Subquery des Vormieters?
Gruß
|
19.10.2010, 14:52:55
|
Junior Member
|
|
Registriert seit: Aug 2010
Alter: 14
Beiträge: 395
|
|
AW: Left Join auf Select
Schon möglich, kannst Du mal den SUB-Select des "Vormieters" extrahieren und darüber den EXPLAIN ausführen. Vielleicht wird dann der EXPLAIN wieder übersichtlicher.
Code:
..
SELECT tempmz.ve,
tempmz.we,
tempmz.kuendigungsdatum,
tempmz.kuendigungsgrund,
Concat(tempa.nachname, ", ", tempa.vorname) AS name,
tempa.tel
FROM tblmieterzuordnung tempmz
INNER JOIN tbladresse tempa
ON tempmz.idadresse = tempa.idadresse
WHERE tempmz.vertragsende = (SELECT Max(TEMP.vertragsende)
FROM tblmieterzuordnung TEMP
WHERE TEMP.ve = tempmz.ve
AND TEMP.we = tempmz.we)
AND tempmz.vertragsende <> '1800-01-01'
..
Grüße
Thomas
__________________
Die SQL-Backstube
Bietet Rezepte, Lösungen und ausführliche Beispiele rund um gesundes SQL und zufriedene Datenbanken.
|
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 15:05:33 Uhr.
|