SQL Abfrage optimieren

tromton · 28.07.2011, 21:50

Hallo Leute,

ich bin dabei eine SQL-Abfrage zu entwerfen. Hier ist der derzeitige Stand:

Code:

SELECT  
`addressbook` . * ,  `addressbook_info_email`.`id_type` AS  `email_id_type` ,  
`addressbook_info_email`.`value` AS  `email_value` ,  
`addressbook_info_telephone`.`id_type` AS  `telephone_id_type` ,  
`addressbook_info_telephone`.`id_ccode` AS  `telephone_id_ccode` ,  
`addressbook_info_telephone`.`value` AS `telephone_value` 
FROM  `addressbook` 
LEFT JOIN  `addressbook_info_email` ON  `addressbook`.`id` =  `addressbook_info_email`.`id_addressbook` 
LEFT JOIN  `addressbook_info_telephone` ON  `addressbook`.`id` =  `addressbook_info_telephone`.`id_addressbook` 
WHERE 1 
GROUP BY  `addressbook`.`id` 
ORDER BY  `addressbook`.`name1` ASC ,  `addressbook`.`name2` ASC ,  `addressbook_info_email`.`rank` ASC ,  `addressbook_info_telephone`.`rank` ASC 
LIMIT 0 , 200

Es ist also eine Address-Datenbank. Jeder Eintrag kann über mehrere E-Mail-Adressen und Telefonnummern verfügen. In der Abfrage möchte ich zu jedem Eintrag die Passende E-Mail-Adresse und Telefonnummer mit dem höchsten Ranking auflisten, falls diese vorhanden ist.

So wie die Abfrage gerade ist, dauert sie etwa 0.5 Sekunden bei 1000 Eointrägen in der Haupttabelle. Ich suche nach Möglichkeiten, das Ganze eleganter zu lösen. Hat jemand eine Idee?

Vielen dank für Tipps und Grüße

Tromton

seb_ · 28.07.2011, 23:56

Ohne die Abfrage im Detail durchgegangen zu sein, habe ich die Vermutung, dass bei dieser geringen Anzahl an Datensätzen (sowohl in der Tabelle als auch in der Ausgabe), fehlende Indizes für die hohe Ladezeit verantwortlich sind.

MfG

tromton · 29.07.2011, 00:35

Zitat:

Zitat von seb_

Ohne die Abfrage im Detail durchgegangen zu sein, habe ich die Vermutung, dass bei dieser geringen Anzahl an Datensätzen (sowohl in der Tabelle als auch in der Ausgabe), fehlende Indizes für die hohe Ladezeit verantwortlich sind.

MfG

danke für deine Antwort. ich hab gerade nochmal nachgescheut. fehlende Indizes sind es nicht. alle ids der beteiligten tables haben einen index gesetzt.
andere Ideen? ggf. Verbesserungsvorschläge für die abfragetechnik (outer left join hit group by usw.)? kann man ggf irgendwie die einträge von den gejointen tabellen für telefon und email von vornherein auf 1 limitieren?

vielen dank und grüße

tromton · 29.07.2011, 00:48

Zitat:

Zitat von seb_

Ohne die Abfrage im Detail durchgegangen zu sein, habe ich die Vermutung, dass bei dieser geringen Anzahl an Datensätzen (sowohl in der Tabelle als auch in der Ausgabe), fehlende Indizes für die hohe Ladezeit verantwortlich sind.

MfG

ich hab gerade nochmal weiter an den Indizes geschraubt, und festgestellt das die fremdschlüssel noch nicht eingetragen waren. insofern lag die langsame abfrage tatsächlich an den fehlenden Indizes. vielen dank für den tip!

optimierung bzgl. der sql abfrage trotzdem erwünscht!

grüße

tromton

php1704 · 29.07.2011, 11:31

Code:

EXPLAIN SELECT `addressbook` . * ,  `addressbook_...

liefert dir u.a. Infos ob/welche Indizies verwendet werden.

Grüße.

tromton · 29.07.2011, 12:33

Zitat:

Zitat von php1704

Code:

EXPLAIN SELECT `addressbook` . * ,  `addressbook_...

liefert dir u.a. Infos ob/welche Indizies verwendet werden.

Grüße.

ah, vielen dank. das hab ich dann mal ausgeführt. phpmyadmin gibt mir die oben angezeigte Tabelle aus. soweit ich das verstehe, sagt sie mir das alle für die abfrage relevanten Indizes gesetzt sind, richtig?

grüße

tromton

fab · 29.07.2011, 13:31

Für die Joins ja, die sind damit optimiert. Aber für die addressbook-Tabelle wird kein Index genutzt und "Using temporary; Using filesort" deutet schon auf eine Bremse hin. Du könntest mal mit einem Index für die Sortierung experimentieren, der aus (name1, name2) besteht. Bedenke aber dass die Indizes auch aufgebaut werden müssen und damit Schreiboperationen länger dauern, ein solcher Index also nur Sinn macht wenn deutlich öfter gelesen als geschrieben wird und du auch messbaren Performancegewinn erreichst.

tromton · 29.07.2011, 14:39

Zitat:

Zitat von fab

Für die Joins ja, die sind damit optimiert. Aber für die addressbook-Tabelle wird kein Index genutzt und "Using temporary; Using filesort" deutet schon auf eine Bremse hin. Du könntest mal mit einem Index für die Sortierung experimentieren, der aus (name1, name2) besteht. Bedenke aber dass die Indizes auch aufgebaut werden müssen und damit Schreiboperationen länger dauern, ein solcher Index also nur Sinn macht wenn deutlich öfter gelesen als geschrieben wird und du auch messbaren Performancegewinn erreichst.

danke für den Hinweis. ich habe ein wenig recherchiert (kenne mich damit noch nicht aus). es hat nicht geholfen die felder die ich sortiere jeweils zu indizieren. gerade stehe ich damit auf dem schlauch. vielleicht hast du einen tip wie es genau indiziert werden soll?

danke

tromton

G.Schuster · 29.07.2011, 21:41

Nur mal so - ein GROUP BY wird idR vor einem ORDER BY gehandelt, d.h. deine Sortierung ist für den Allerwertesten, wenn sie nicht zufällig passt.

tromton · 29.07.2011, 21:49

Zitat:

Zitat von G.Schuster

Nur mal so - ein GROUP BY wird idR vor einem ORDER BY gehandelt, d.h. deine Sortierung ist für den Allerwertesten, wenn sie nicht zufällig passt.

vielen dank für den Hinweis. wie kann man den die gejointen tabellen vor dem groupby sortieren? bzw dieses problem lösen.
bisher hat es wohl eher aus Zufall gepasst. über einen anstoß zur Lösung würde ich mich sehr freuen.

vielen dank und grüße

tromton

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
sql abfrage unterschied where, join	kaptainIglo	Datenbanken	8	15.06.2011 13:09
[Erledigt] SQL Abfrage escapen	wooha	PHP Einsteiger	3	18.04.2011 10:13
[Erledigt] Verdraxte Abfrage von 2 SQL Tabellen	EmmKey	PHP Einsteiger	7	15.03.2011 12:21
[Erledigt] SQL zweistufige Abfrage	Elefterios	Datenbanken	12	13.03.2011 15:23
SQL Abfrage INNER JOIN etc.	chr1s-eg	Datenbanken	4	01.03.2011 13:37
SQL Abfrage - Summieren mehrerer Werte eines Datensatzes	themk#	Datenbanken	7	15.02.2011 16:42
SQL Abfrage über PHP	cURE	Datenbanken	3	09.02.2011 12:55
Array aus SQL Abfrage zusammenbauen um es rekursiv abzuarbeiten	litterauspirna	PHP Einsteiger	7	01.02.2011 18:44
Werte mehrerer Checkboxen für SQL Abfrage übernehmen?	krais	PHP Einsteiger	4	14.01.2011 21:24
Umlaute (Ã¤) in der SQL Abfrage	noop	Datenbanken	5	09.01.2011 23:06
[Erledigt] Mehr als eine SQL Abfrage hintereinander.	Seebär	PHP Einsteiger	5	29.12.2010 15:44
erweitere sql abfrage	Helix	PHP Einsteiger	5	27.12.2010 04:11
False Abfrage und SQL hängt sich auf	marc77	Datenbanken	4	22.11.2010 14:57
Wie geht diese SQL Abfrage?	marc77	Datenbanken	5	13.11.2010 12:50
[Erledigt] sql abfrage mit Tabelle in der $ Zeichn vorkommt	dior	Datenbanken	5	09.11.2010 13:05

28.07.2011, 21:50
tromton Neuer Benutzer Registriert seit: 28.07.2011 Beiträge: 14 PHP-Kenntnisse: Fortgeschritten	SQL Abfrage optimieren Hallo Leute, ich bin dabei eine SQL-Abfrage zu entwerfen. Hier ist der derzeitige Stand: Code: SELECT `addressbook` . * , `addressbook_info_email`.`id_type` AS `email_id_type` , `addressbook_info_email`.`value` AS `email_value` , `addressbook_info_telephone`.`id_type` AS `telephone_id_type` , `addressbook_info_telephone`.`id_ccode` AS `telephone_id_ccode` , `addressbook_info_telephone`.`value` AS `telephone_value` FROM `addressbook` LEFT JOIN `addressbook_info_email` ON `addressbook`.`id` = `addressbook_info_email`.`id_addressbook` LEFT JOIN `addressbook_info_telephone` ON `addressbook`.`id` = `addressbook_info_telephone`.`id_addressbook` WHERE 1 GROUP BY `addressbook`.`id` ORDER BY `addressbook`.`name1` ASC , `addressbook`.`name2` ASC , `addressbook_info_email`.`rank` ASC , `addressbook_info_telephone`.`rank` ASC LIMIT 0 , 200 Es ist also eine Address-Datenbank. Jeder Eintrag kann über mehrere E-Mail-Adressen und Telefonnummern verfügen. In der Abfrage möchte ich zu jedem Eintrag die Passende E-Mail-Adresse und Telefonnummer mit dem höchsten Ranking auflisten, falls diese vorhanden ist. So wie die Abfrage gerade ist, dauert sie etwa 0.5 Sekunden bei 1000 Eointrägen in der Haupttabelle. Ich suche nach Möglichkeiten, das Ganze eleganter zu lösen. Hat jemand eine Idee? Vielen dank für Tipps und Grüße Tromton


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

28.07.2011, 23:56
seb_ Benutzer Registriert seit: 29.05.2011 Beiträge: 58 PHP-Kenntnisse: Fortgeschritten	Ohne die Abfrage im Detail durchgegangen zu sein, habe ich die Vermutung, dass bei dieser geringen Anzahl an Datensätzen (sowohl in der Tabelle als auch in der Ausgabe), fehlende Indizes für die hohe Ladezeit verantwortlich sind. MfG __________________ Lesen gefährdet Ihre Dummheit!

29.07.2011, 11:31
php1704 Erfahrener Benutzer Registriert seit: 13.05.2011 Beiträge: 188 PHP-Kenntnisse: Fortgeschritten	Code: EXPLAIN SELECT `addressbook` . * , `addressbook_... liefert dir u.a. Infos ob/welche Indizies verwendet werden. Grüße.

29.07.2011, 13:31
fab Erfahrener Benutzer Registriert seit: 28.07.2010 Beiträge: 2.308 PHP-Kenntnisse: Fortgeschritten	Für die Joins ja, die sind damit optimiert. Aber für die addressbook-Tabelle wird kein Index genutzt und "Using temporary; Using filesort" deutet schon auf eine Bremse hin. Du könntest mal mit einem Index für die Sortierung experimentieren, der aus (name1, name2) besteht. Bedenke aber dass die Indizes auch aufgebaut werden müssen und damit Schreiboperationen länger dauern, ein solcher Index also nur Sinn macht wenn deutlich öfter gelesen als geschrieben wird und du auch messbaren Performancegewinn erreichst. __________________ PHP Blog + Library CSS Effects + Tools

29.07.2011, 21:41
G.Schuster Erfahrener Benutzer Registriert seit: 21.12.2009 Beiträge: 415 PHP-Kenntnisse: Fortgeschritten	Nur mal so - ein GROUP BY wird idR vor einem ORDER BY gehandelt, d.h. deine Sortierung ist für den Allerwertesten, wenn sie nicht zufällig passt. __________________ actra.development - Schwabstr. 2 - 70825 Münchingen www.actra.de/velopment/ - eMail: php.de@actra.de Zend Certified Engineer for PHP5