SQL Query Optimierung mit prepared statements in php

[lyzerak]

Hallo Leute

Ich habe ein Query welches mir zwei DBs absucht und eis Resultat ausgibt.

Schlussendlich sieht das Resultat +/- so aus:

USERNAME----POSITIVE----NEUTRAL----NEGATIVE----TOTAL TRADES-----IS TRUSTED------IS MIDDLEMAN

blabla----------1--------------0--------------0---------------1---------------------YES----------------NO
userA----------1--------------1--------------0---------------2---------------------NO-----------------NO

usw.

Nun möchte ich aber, dass die Einträge nur angezeigt werden, wenn das Feld review_done = 1 auf der tradeDB gesetzt ist. Das momentane Query sieht so aus:

...
    $sql = 'SELECT username,
           	   (SELECT COUNT(*) FROM tradeDB WHERE tradeDB.rating_for = 
                                                 userDB.username AND tradeDB.rating = 5),
   	    	   (SELECT COUNT(*) FROM tradeDB WHERE tradeDB.rating_for = 
                                                 userDB.username AND tradeDB.rating = 3),
   		   (SELECT COUNT(*) FROM tradeDB WHERE tradeDB.rating_for = 
                                                 userDB.username AND tradeDB.rating = 1),
        	   (SELECT COUNT(*) FROM tradeDB WHERE tradeDB.rating_for = 
                                                 userDB.username),	
		   (CASE WHEN is_trusted_trader=1 THEN "YES" ELSE "NO" END),
		   (CASE WHEN is_middleman=1 THEN "YES" ELSE "NO" END)  
	      FROM u2akx_userDB';
			               
    // Prepare statement
    $result = $db->prepare( $sql );
    // Bind results to depending variables
    $result->bind_result($username,$positive,$neutral,$negative,$totalrating, $isMiddleman  ,$isTrustedTrader);
    // Send statement to DB
    $result->execute();
    // Store the results in cache          
    $result->store_result();
...

Weiss irgendwie nicht mehr weiter

Danke

 

[Daaron]

Das hat weniger was mit Prepared Statements zu tun sondern mit der Struktur deiner Abfrage.
Arbeite mit JOINs, nicht mit Unter-Selects, dann wird das auch was.... und es wird im Zweifel x-fach schneller.

 

[r15ch13]

Sind tradeDB und userDB richtige Datenbanken oder Tabellen?

Wenn es Datenbanken sind, dann muessten die Unterselects anders lauten:

SELECT COUNT(*) FROM tradeDB.tradeTabelle WHERE tradeDB.tradeTabelle.rating_for = userDB.userTabelle.username AND tradeDB.tradeTabelle.rating = 5

PS: is_trusted_trader und is_middleman kannst du doch einfach als Boolean in PHP weiter verwenden. Dann brauchst du kein YES/NO.

 

[lyzerak]

Hi Daaron,

Hast du KONKRET ein Beispiel mit meinen Statements? Ich würde nicht hier Fragen wenn ich weiter wüsste.

Hi r15ch13

Danke sind Tabellen. Ich habe nur eine DB mit eben diesen Tabellen. Wundert mich, dass es funktioniert mit meiner Notation! Leidet da die Performance wenn man es nicht voll-qualifiziert angibt?



Danke

 

[Daaron]

Hast du KONKRET ein Beispiel mit meinen Statements? Ich würde nicht hier Fragen wenn ich weiter wüsste.

Nö, grad nicht... aber ohne dein Tabellenschema zu kennen kann man da auch kaum ausmisten.

Danke sind Tabellen. Ich habe nur eine DB mit eben diesen Tabellen. Wundert mich, dass es funktioniert mit meiner Notation! Leidet da die Performance wenn man es nicht voll-qualifiziert angibt?

Wenn es Tabellen sind, warum heißt es dann ...DB? Das macht man nicht.
Und somit klärt sich auch die Frage nach der Angabe.... so viel wie nötig, so wenig wie möglich. Das RDBMS weiß schon, was es zu tun hat.

Wie gesagt, hier sollte eher das Tabellenschema an sich überdacht werden. Evtl. kann man hier noch deutlich optimieren. Hier seh ich z.B. gar keine Verbindung zwischen der äußeren u2akx_userDB und der inneren tradeDB

 

[r15ch13]

Mich hat jetzt der Name der Tabellen gestoert, da man in der Regel nicht DB an den Namen anhaengt.
Die Performance leidet darunter das du Unter-Selects verwendest. Ein Beispiel fuer die Verwendung von JOINs sieht so aus.
Habe die Tabellen Struktur jetzt nicht nachgebaut, aber schau mal ob das funktioniert.

SELECT userDB.username, COUNT(userDB.username) AS tradesPerRating, tradeDB.rating, userDB.is_trusted_trader, userDB.is_middleman
FROM userDB
LEFT JOIN tradeDB ON tradeDB.rating_for = userDB.username
GROUP BY userDB.username, tradeDB.rating;

username    rating    tradesPerRating    is_trusted_trader    is_middleman
blabla      5         1                  1                    0
userA       5         1                  0                    0
userA       3         1                  0                    0

Als Ergebnis bekommst die User nach Rating gruppiert. User werden dadurch zwar mehrmals pro Rating aufgelistet aber, das kann man spaeter so bearbeiten wie man es braucht.

Um Daaron noch zu ergaenzen: Du solltest in der tradeDB nicht den Usernamen sondern die ID des Users speichern. Stichwort ForeignKey

 

[gaunt]

Eigentlich steht da schon alles was du brauchst um das Problem zu lösen:
https://www.computerbase.de/forum/threads/sql-abfrage-bitte-um-unterstuetzung-bez-query.1314789/

Optimiere deine Tabellen und vergiss die SubSelects!
Daaron und r15xyz haben nochmal alles gesagt wass du wissen must.

Wenn du es absolut nicht hinbekommst dann poste wenigstens die Creates der Tabellen. Dann kann man auch mal schnell nen Select schreiben (oder die Tabellen optimieren). Aber jetzt erst noch Tabellen mit Testdaten anlegen... Nein!