MySQL - Abfrage (Ausgabe) zugroß.

[Strafi]

Hallo,

ich stelle ein Anfrage mit PDO und verwende SMARTY und das Paginator plugin.

Die Anfrage geht über mehre Tabellen und das alles funktioniert auch super. Nur kann es vor kommen das zum Beispiel 100.000 Datensätze ausgegeben werden und dann braucht der ne ganze weile bevor das fertig ist mit cachen usw.

Da ich den Paginator benutze stelle ich 2 Anfragen. Eine zum ermitteln der Anzahl von Datensätzen und dann die Ausgaben.

Wie kann ich das Optimieren? Also so das nicht bei 100.000 Einträgen das Ding so rattert. Ich brauch aber ein Zugriff auf alle Datensätze.



Grüße

 

[Yaslaw]

Um ein SQL zu optimieren ist es Sinnvoll das SQL zu sehen.
Ansonsten gibt es ganze Bücher über das Thema Performance-Optimierung. Das kann man dir hier nicht einfach so in 2 Sätzen allgemein beantworten

 

[Strafi]

So hier mal mein SQL:

$qry = $db->prepare("
			SELECT SQL_CALC_FOUND_ROWS DISTINCT
				r.id
				
			FROM
				anfragen r
			LEFT JOIN
				transa t
			ON
				r.id = t.a_id
			LEFT JOIN
				frontend u
			ON
				r.user_id = u.id
			LEFT JOIN
				anbieter a
			ON
				a.id=r.anbieter_id
			LEFT JOIN
				(SELECT * FROM status WHERE tid='44') as s
			ON
				s.tranid = t.id
			
			".$where."
			ORDER BY
				r.datum DESC
		");
		$qry->execute();		
		SmartyPaginate::setTotal($qry->rowCount());
		
		$qry = NULL;
		
		$qry = $db->prepare("
			SELECT DISTINCT
				r.*,
				t.*,
				a.*,
				u.*
				
				
			FROM
				anfragen as r
			LEFT JOIN
				transa as t
			ON
				r.id = t.a_id
			LEFT JOIN
				frontend as u
			ON
				r.user_id = u.id
			LEFT JOIN
				anb as a
			ON
				a.id=r.anb_id
			LEFT JOIN
				(SELECT * FROM status WHERE tid='44') as s
			ON
				s.transaid = t.id

			".$where."
			ORDER BY
				r.datum DESC
			LIMIT
				".SmartyPaginate::getCurrentIndex().",".SmartyPaginate::getLimit()."
		");
		$qry->execute();
		$requests = $qry->fetchAll();

der erste um die Anzahl der Einträge zu ermitteln. Pagination läuft über das Smarty->plugin

schön wäre es vielleicht die Anzahl der Datensätze gleich zu ermitteln, SQL_CALC_FOUND_ROWS nur muss da noch was passieren.


Die Einträge aus Tabelle Status können ja mehre sein, die ordne ich später mit einer Schleife und trage jeden Datensatz die Einträge aus Status in einem Feld zu, getrennt durch Komma.

Vielleicht ist das auch ein unnötiger Schritt. Schön wäre es ja alle Einträge aus Status dem Datensatz schon beim SQL hinzu zuordnen, doch habe ich dazu leider keine Idee bzw. sind mir nicht alle Möglichkeiten von MySQL bewusst.

 

[Yaslaw]

Um die Anzahl zu ermitteln schon mal 2 Punkte:
1) Der ORDER BY verbraucht viel Zeit und ist für die Anzahl total irelevant (= Performance-Fresser).
2) Für die Anzahl sind nur die LEFT JOINS notwednig, bei denen mehrere Eintrage pro anfrage vorhanden sind oder die im WHERE verwednet werden.
zB Anbieter: Ich denke mal, da gibts pro Antrag ein oder kein Anbieter. Aber nicht mehrere. Ergo ist dieser LEFT JOIN für den Count irrelevant ausser der WHERE verweisst auf die Tabelle

Zu deiner eigentlichen Abfrage
1) Nimm nicht alle Felder aller Tabellen. Reduziere den SELECT-Bereich auf dei Felder die du auch wirklich brauchst.
2) die Staties willst du mit Kommas getrennt anzeigen? Dann mach das gleich im SQL. Mach ein GROUP BY und füge die Staties mittels GROUP_CONCAT zusammen. Das reduziert die Anzahl Zeilen und du kannst dir eine komplizierte PHP-Logik sparen

...
LEFT JOIN (
	SELECT 
		transaid,
		GROUP_CONCAT(statusname ORDER BY statusorder SEPARATOR ', ') AS states
	FROM 
		`status` 
	WHERE tid='44'
	GROUP BY
		transaid
	) as s 	
	ON s.transaid = t.id 
...

 

[Strafi]

Kann ich mir mit dem SQL_CALC_FOUND_ROWS die anfrage für dei Anzahl sparren?

achso und anbieter sind mehrere, wenn ich eine suche ohne where anfrage muss er mir ja alles zurückgeben, dei anderen anfragen, wenn ich ja nur nach anbieter oder und suche, gibt mir ja die anzahl der such anfrage zurück...

ps.: ich nehme nicht alle felder, habe das nur gekürzt für den beitrag hier

 

[Yaslaw]

Kann ich mir mit dem SQL_CALC_FOUND_ROWS die anfrage für dei Anzahl sparren?

Nein. Denn die Tabelle `anfrage`ist die Zentrale Datentabelle in diesem Query. Die brauchst du immer.

achso und anbieter sind mehrere, wenn ich eine suche ohne where anfrage muss er mir ja alles zurückgeben, dei anderen anfragen, wenn ich ja nur nach anbieter oder und suche, gibt mir ja die anzahl der such anfrage zurück...

Gut, das wusste ich mangels Informationen nicht.
Aber wenn du die Staties nachher eh kommasepariert haben willst, kannst du sie getrost aus der Zähl-Abfrage entfernen. Denn diese ergebn mit meinem GROUP_CONCAT() nur noch eine Zeile.

Hat es was genützt den ORDER BY aus der Zähl-Abfrage zu entfernen?
Hat es was genützt die Staites mit dem GROUP_CONCAT zusammenzufassen um dei Anzahl Zeilen zu verringern?

Wie hat sich der Explain-Plan verändert? Wenn du nicht weisst was das ist, dann google mal. Der ist sehr Zentral beim verbssern der Performance von SQL-Statements.

 

[Strafi]

		$qry = $db->prepare("
			SELECT DISTINCT
				r.datum,
				r.id,
				r.user_id,
				r.name,
				r.vorname,
				r.geschlecht,
				r.geburts_tag,
				r.geburts_monat,
				r.geburts_jahr,
				r.strasse,
				r.hnr,
				r.hb,
				r.hz,
				r.plz,
				r.ort,
				r.verwendungszweck,
				a.name,
				t.timeout,
				t.preis,
				t.preis as vk,
				t.id as transaction_id,
				u.login
				
				
			FROM
				anfragen as r
			LEFT JOIN
				transaktionen as t
			ON
				r.id = t.a_id
			LEFT JOIN
				frontend_user as u
			ON
				r.user_id = u.id
			LEFT JOIN
				anbieter as a
			ON
				a.id=r.anbieter_id
			LEFT JOIN (
				SELECT 
					s.id,
					GROUP_CONCAT(s.id SEPARATOR ', ') AS x_44es
				FROM 
					stati 
				WHERE
					tableid='44'
				GROUP BY
					s.id
				)
				AS
					s  
				ON
					s.transactionid = t.id 

			".$where."
			ORDER BY
				r.datum DESC
			LIMIT
				".SmartyPaginate::getCurrentIndex().",".SmartyPaginate::getLimit()."
		");

Funktioniert nicht... habe bestimmt ein Fehler.... an der falschen Stelle?

 

[Yaslaw]

jepp. Im subquery gruppierst du nach id und gibsts auch nur das Feld is weiter. Aber im LEFT JOIN prüfst du auf transactionid

Am besten testet man die SQLs mittels phpMyAdmin oder so. Siehe hierzu auch das Tutorial PHP MySQL Debug Queries

 

[Strafi]

Okay ich aheb jetzt eine ausgabe, auf die ich fast 2 minuten gewartet habe. Vielleicht stimmt auch was nicht mit dem server.


habe es jetzt so:

LEFT JOIN (

SELECT transactionid, GROUP_CONCAT( id
SEPARATOR ', ' ) AS x_44es
FROM stati
WHERE tableid = '44'
GROUP BY transactionid
) AS s ON s.transactionid = t.id

und habe auch den kompletten Query im PHPAdmin geprüft.
Das Feld x_44es gibt es nicht.

 

[Yaslaw]

Das Feld x_44es gibt es nicht.

Wo gibt es das Nicht?
Hast du das Subquery auch mal alleine gestestet?

Nun zu deinen Performance-Problem:
Was sagt EXPLAIN?
Hast du Sinnvolle Indexe?
Kannst du deine Indexe so anlegen, dass gff FillIndexScans anstelle von FullTableScans durchgeführt werden können?

So und nun mein Abschlusswort zu diesem Thread.
Performance-Verbesserungen sind keine eindeutige und einfache Sache. Es ist ein testen und heran tasten. Auswerten von Explainplans, SQL umschreiben - ggf. total umschreiben, weiter testen, Anzahl Datensätze der Tabelle vergleichen, ggf. Subqueries machen um die Datenmenge so früh wie möglich klein zu kriegen etc.
Also, so einfach *schnipp*und ein SQL ist schnell - das kannst du vergessen. Diese ganzen Punkte die ich aufgezählt habe gehen auch nicht einfach so mit im Forum hin und her schreiben. Lies dich in das Thema ein. Verscuh es zu verstehen. Und dann setz dich hin und probier dich durch.