SQL SQL-Abfrage optimieren

[schumischumi]

Hi,

ich steh anscheinend zur Zeit bissl auf dem Schlauch und benötige eure Hilfe.
und zwar habe ich eine kleine inventarisierungoberfläche in php gebaut aber eine spezielle MySQL Abfrage dauert "ewig" (> 2 Sekunden).
Die Struktur sieht so aus:

--------------------------------- 
| Tabelle1                      |
--------------------------------- 
| Gerätename (String) / PK      |
| IP (String)                   |
| MAC (String)                  |
| ...                           |
---------------------------------

---------------------------------
| Tabelle2                      |
---------------------------------
| Gerätename                    |
| Softwarename                  |
---------------------------------

Das ganze ist eine n zu n Beziehung und Tabelle 2 ist folgendermaßen und wahrscheinlich eher suboptimal aufgebaut:

Gerätename        Softwarename
------------      -----------------
notebook1         nero
notebook1         notepad
notebook1         calc
notebook2         calc
notebook2         iexplorer

Dh es sind alle Geräte aus Tabelle 1 mit jeweils jeder installierten SW aufgelistet. Insgesamt umfasst diese tabelle 360000 Einträge da für jede Software pro Gerät ein neuer eintrag gemacht wird.

Der Abfragestring sieht folgendermaßen aus:

select Gerätename, IP, MAC from Tabelle1 left join Tabelle2 on (Gerätename.Tabelle1 = left(Gerätename.Tabelle2,6)) where Softwarename.Tabelle1 like 'Nero';

Der Left-Befehl bei den Join Bedingungen wird deshalb verwendet, da der inventarisierungsclient das ganze eher unsauber (mit Zeilenumbruch bzw. \n) abspeichert. Das könnte ich allerdings noch beheben.


wie könnte ich die Tabellen sinnvoller aufbauen um mehr Performance zu bekommen?


Thx für eure Zeit

 

[SymA]

Hast du die Abfrage direkt in der Konsole ausführen lassen? Nicht, dass die 2 Sekunden von etwas anderem kommen und die eigentlich Abfrage nur ein paar Millisekunden dauert.

 

[Simon]

Indizes gesetzt?

 

[coldcut]

Was für ein Datebanksystem?
Hast du die Tabellen bzw. die wichtigen Spalten indiziert? Ist Tabelle 2 Gerätename als FK definiert?
Wenn du MySQL oder PGSQL hast, probier es mal mit explain: http://dev.mysql.com/doc/refman/5.0/en/explain.html

 

[tRITON]

DU machst halt immer String vergleiche, die dauern.

Mach doch für die Software eine eigene Tabelle die du dann per ID joinst, dann wird die Abfrage sicher schneller laufen (unabhängig, was SymA schon als Tipp gegeben hat)

 

[Yuuri]

Der Query sieht extrem komisch aus. Auch nichts vertauscht?

Hinter left join fehlt die Tabelle, mit der du jonst.
<Spaltenname>.<Tabelle>?

Am besten du gehst mal mit einem Profiler durch - XDebug. Zum Anzeigen der Daten kannst du KCacheGrind verwenden - gibts auch für Windows.

 

[schumischumi]

danke für die schnellen antworten.
@Yuuri: war ein Tippfehler wurde ausgebessert.
@rest: Das Datenbanksystem ist MySQL und ja ich hab das ganze direkt in der Konsole bzw. in der Workbench laufen lassen. Da kommen ca 1600 Millisekunden bei raus.
Das Problem ist, dass ich mir nicht 100%ig auf die IDs der Tabelle1 verlassen kann oder eher will. Die Einträge in der Tabelle1 werden automatisch und unregelmäßig erzeugt bzw. geupdatet. dies übernimmt ein proprietärer inventarisierungsclient. Ich kann leider nicht zu 100% sagen, woran sich dieser Client orientiert (ID oder Hostname), da wir auch schon Einträge mit gleichen Gerätenamen hatten. Die Tabelle2 wird aus TXT-Dateien erzeugt die uns der Client reinliefert.
naja hilft wohl nix dann guck ich mal in ein paar tests ob die ID sich ändert wenn der clients in der SQL-DB updatet und geh dann auf die IDs statt der Gerätenamen und setzte dementsprechend die indizies.

danke ncohmal

 

[Drexel]

Also normalerweise baut man n zu n Beziehung so auf.

Tabelle 1
ID|Geraetename

Tabelle 2
GeraeteID|SoftwareID

Tabelle 3
ID|Softwarename


Alles andere wurde auch schon genannt, Indizes, String Vergleiche etc. Und auch noch ne String Funktion (left) im Join Kriterium, das dürfte fast schond er Hauptübeltäter sein. Da geht jeder Query Optimizer auf die Bretter.

 

[schumischumi]

hab das join auf die IDs gelegt und indexies eingeführt. Ergebnis ist hammer:
- ohne anpassungen: 1600ms
- join auf IDs ohne index: 650ms
- join auf IDs mit index: 4ms

thx für eure hilfe