[dbs] Abfrageoptimierung

[Sebastian Tobias Mendel genannt Mendelsohn]

Sven wrote:

> Hallo
> 
> Ich habe eine Abfrage, die je mehr Ergebnisse sie liefert, weit mehr als 4 
> Sekunden dauern kann. Das muss doch irgendwie zu optimieren sein.
> Mir fällt allerdings nichts mehr dazu ein.
> 
> Hier die Abfrage:
> SELECT kfz_firmen.f_id, kfz_firmen.firma, kfz_firmen.strasse, kfz_firmen.plz, 
>        kfz_firmen.ort, kfz_firmen.accountart, kfz_firmen.akin
> FROM kfz_firmen, kfz_automarke, kfz_region, kfz_service
> LEFT JOIN kfz_zu_af ON ( 
>     kfz_firmen.f_id = kfz_zu_af.f_id AND kfz_automarke.a_id = kfz_zu_af.a_id)
> LEFT JOIN kfz_zu_rf ON ( 
>     kfz_firmen.f_id = kfz_zu_rf.f_id AND kfz_region.r_id = kfz_zu_rf.r_id ) 
> LEFT JOIN kfz_zu_sf ON ( 
>     kfz_firmen.f_id = kfz_zu_sf.f_id AND kfz_service.s_id = kfz_zu_sf.s_id ) 
> WHERE kfz_firmen.akin = '1' AND kfz_automarke.akin = '1' AND 
>       kfz_region.akin = '1' AND kfz_service.akin = '1' AND 
>       kfz_service.s_id = '25' AND kfz_zu_sf.s_id = '25'
> GROUP BY kfz_firmen.firma, kfz_firmen.strasse
> ORDER BY kfz_firmen.accountart, kfz_firmen.firma
> LIMIT 0 , 10 
> 
> Anmerkungen dazu:
> Es gibt 4 Tabellen mit Hauptinhalten, wobei sich alles auf die Tabelle 
> "kfz_firmen" bezieht.
> 3 Tabellen (kfz_zu_af, kfz_zu_rf, kfz_zu_sf) beinhalten die Verknüpfungen der 
> Tabellen kfz_automarke, kfz_region und kfz_service mit der Tabelle 
> kfz_firmen.
> Die Verknüpfungstabellen sehen so aus (Beispiel kfz_zu_af):
> af_id | a_id | f_id
> af_id - Primary
> a_id - Index
> f_id - Index
> 
> Die akin-Spalten sind vom Typ "enum" und beinhalten nur 1 oder 0.
> 
> So, und dann hab ich zu dieser Abfrage noch eine EXPLAIN-Ausgabe (Erzeugt mit 
> phpMyAdmin):
> 
> +---------------+-------+---------------+---------+---------+
> | table         | type  | possible_keys | key     | key_len |
> +---------------+-------+---------------+---------+---------+
> | kfz_service   | const | PRIMARY,akin  | PRIMARY |       4 |
> | kfz_firmen    | range | akin          | akin    |       1 |
> | kfz_automarke | ALL   | akin          | NULL    |    NULL |
> | kfz_zu_af     | ref   | a_id,f_id     | a_id    |       4 |
> | kfz_region    | ALL   | akin          | NULL    |    NULL |
> | kfz_zu_rf     | ALL   | f_id,r_id     | NULL    |    NULL |
> | kfz_zu_sf     | ref   | f_id,s_id     | f_id    |       4 |
> +---------------+-------+---------------+---------+---------+
> 
> +---------------+-------------------+------+--------------------------------+
> | table         | ref               | rows | Extra                          |
> +---------------+-------------------+------+--------------------------------+
> | kfz_service   | const             |    1 | Using temporary; Using filesort|
> | kfz_firmen    | NULL              |  132 | Using where                    |
> | kfz_automarke | NULL              |   24 | Using where                    |
> | kfz_zu_af     | kfz_automarke.a_id|   12 |                                |
> | kfz_region    | NULL              |   30 | Using where                    |
> | kfz_zu_rf     | NULL              |   25 |                                |
> | kfz_zu_sf     | kfz_firmen.f_id   |   12 | Using where                    |
> +---------------+-------------------+------+--------------------------------+
> 
> Wo kann man hier mit einer Optimierung ansetzen?
> 

du hast vergessen zu sagen WAS die Abfrage denn liefern soll?

    SELECT kfz_firmen.f_id,
           kfz_firmen.firma,
           kfz_firmen.strasse,
           kfz_firmen.plz,
           kfz_firmen.ort,
           kfz_firmen.accountart,
           kfz_firmen.akin
      FROM kfz_service
LEFT JOIN kfz_zu_sf
        ON kfz_service.s_id = kfz_zu_sf.s_id
LEFT JOIN kfz_firmen
        ON kfz_firmen.f_id = kfz_zu_sf.f_id
LEFT JOIN kfz_zu_af
        ON kfz_firmen.f_id = kfz_zu_af.f_id
LEFT JOIN kfz_automarke
        ON kfz_automarke.a_id = kfz_zu_af.a_id
LEFT JOIN kfz_zu_rf
        ON kfz_firmen.f_id = kfz_zu_rf.f_id
LEFT JOIN kfz_region,
        ON kfz_region.r_id = kfz_zu_rf.r_id
     WHERE kfz_service.s_id = '25'
       AND kfz_service.akin = '1'
       AND kfz_automarke.akin = '1'
       AND kfz_region.akin = '1'
       AND kfz_firmen.akin = '1'
  GROUP BY kfz_firmen.f_id
  ORDER BY kfz_firmen.accountart,
           kfz_firmen.firma
     LIMIT 0, 10

... mhm auf jeden fall sollten deine ORDER BY - Spalten auch im index 
sein!!!

   kfz_firmen INDEX(f_id, accountart, firma)

... außerdem sollte die allererste Tabelle, also die direkt nach dem 
fROM sollte die sein die die wenigsten Datansätze zurückliefert, in 
diesem Fall wohl kfz_service

... das GOUP BY sollte natürlich auch auf NUR auf indizierte Spalten 
angweandt werden!!!

Grundsätzlich: es sollten möglichst ALLE Saplten die in WHERE, ORDER BY 
oder GROUP BY verwendet werden in einem Index vorkommen

'Using temporary; Using filesort' ist das Schlechteste was überhaupt da 
stehen kann bei EXPLAIN (hast du ein index auf kfz_service.s_id ???)

hast die Indizes auf die akin-Spalten?

in kfz_service ein Index auf s_id,akin

-- 
Sebastian Mendel

*www.warzonez.de*
www.sebastianmendel.de | www.tekkno4u.de | www.nofetish.com