:: Buchempfehlung ::

thomas_w · #11 14.10.2010, 12:31:41

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

nagel · #12 14.10.2010, 12:51:34

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 |
+----+--------------------+------------+--------+-------------------------------------------------+-----------------------------------+---------+-----------------------------------------------+------+-------------+

thomas_w · #13 14.10.2010, 13:08:44

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

nagel · #14 14.10.2010, 13:51:58

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

thomas_w · #15 14.10.2010, 17:16:52

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

nagel · #16 18.10.2010, 08:44:59

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ß

nagel · #17 19.10.2010, 11:03:47

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 |
+----+--------------------+------------+--------+-------------------------------------------------+---------------------------------+---------+-----------------------------------------------+------+--------------------------+

thomas_w · #18 19.10.2010, 13:46:48

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

nagel · #19 19.10.2010, 14:09:54

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ß

thomas_w · #20 19.10.2010, 14:52:55

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

:: Buchempfehlung ::

:: Anbieterverzeichnis ::

Globale Branchen

Informieren Sie sich über ausgewählte Unternehmen im Anbieterverzeichnis von SELFPHP

:: Newsletter ::

Abonnieren Sie hier den kostenlosen SELFPHP Newsletter!