Ressourcenschonende Bestenliste mit Platzierungseinträgen

[Andi Reas]

Hallo,

ich bin gerade dabei, eine Art Bestenliste zu erstellen, in der die ersten fünf Besten permanent zur Schau gestellt werden - sowie die Platzierung des jeweiligen Teilnehmers selbst.

1. Mario
2. Luigi
3. Peach
4. Wile E. Coyote (!?)
5. Bowser

--

1043. DU

Dafür verwende ich eine klassische HTML - Javascript - PHP - MYSQLI Architektur - bisher speicherte ich nur die Namen, die Punkteanzahl sowie eindeutige ID der User in der Datenbank.

Nun stellt sich die Frage, wie die Abfrage der aktuellen Platzierung möglichst ressourcenschonend für den Server passieren kann - macht es einen Unterschied, ob ich bei jeder erneuten clientseitigen Abfrage der Bestenliste die aktuelle Platzierung serverseitig neu generiere (dafür müsste ja ein Query über sämtliche Datenbankeinträge erfolgen, um neben der Top 5 auch die eigene Platzierung zu ermitteln) oder ob ich meine Datenbank um eine Spalte "Platzierung" ergänze (in diesem Fall wiederum müsste die gesamte Datenbank bei jeder Änderung der Punkteanzahl auf eine Änderung der Spalte "Platzierung" durchleuchtet werden)?

(Will nicht wieder konzeptlos einfach drauflosschießen...)

Andi Reas freut sich auf EURE Gedanken zu dem Thema.

[MudGuard]

Hi,

Nun stellt sich die Frage, wie die Abfrage der aktuellen Platzierung möglichst ressourcenschonend für den Server passieren kann -

Wie sind die Rahmenbedingungen?

Also z.B.:

• wie oft wird die Liste abgefragt?
• wie oft ändert sich die Liste?
• wie viele Einträge hat die Liste?

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo Andi,

wenn "Du" auf Platz 1043 tatsächlich eine realistische Darstellung ist, klingt das nach einer recht umfangreichen Datenbank. Die Frage nach dem Datenumfang - also wie viele User und wie viele Rows pro User - ist deshalb entscheidend. Die Frage nach der Abfragehäufigkeit ebenfalls. Machen die User das gezielt? Oder ist dieses Ranking etwas, was auf einer oft abgerufenen Homepage steht?

Die Platzierung selbst als Datenattribut zu speichern ist aber etwas, dass Du nicht einplanen solltest. Die solltest Du - wenn Du MYSQL 8 oder eine neuere MariaDB hast, mit der RANK-Funktion bestimmen.

Rolf

[Andi Reas]

Hallo,

RANK() klingt ja sehr fancy, aber tut es eine ganz triviale ORDER BY SQL Abfrage nicht ebenso? (Dem dann ja in korrekter Reihenfolge vorliegenden Abfrageergebnis könnte ich in einer PHP Schleife eine Platzierung verpassen, bevor ich das Endresultat an den Client zurückgebe).

...oder ist RANK() da performativer / ressourcenschonender?

Ich werde also nicht umhin kommen, bei jeder Abfrage der Bestenliste selbige serverseitig per SQL Abfrage komplett neu zu erstellen (i.e. aktualisieren), www.großesSeufz.com.

Eine Idee wäre da, die Abfrage mit LIMIT zumindest so begrenzen, dass die Tabelle nur bis zum Teilnehmer ausgegeben wird - "schlechtere" Einträge nach dem Teilnehmer dann nicht mehr (irrelevant).

Bin für jeden weiteren Optimierungsvorschlag (/ Alternativvorschlag?) dankbar!

LG, Andi

[Rolf B]

Hallo Andi,

RANK() klingt ja sehr fancy, aber tut es eine ganz triviale ORDER BY SQL Abfrage nicht ebenso? (Dem dann ja in korrekter Reihenfolge vorliegenden Abfrageergebnis könnte ich in einer PHP Schleife eine Platzierung verpassen,

Fancy ist dein Synonym für "kenn ich nicht, kapier ich nicht"? Kann ich durchaus verstehen, im MYSQL/PHP Bereich sind die Window-Funktionen nicht so bekannt, glaube ich. Aber sie können eine Menge Arbeit sparen.

Du hast nicht gesagt, wo die Punktezahl herkommt, nach der Du den Rang bestimmst. Hast Du eine Row pro - hm - Spieler, oder musst Du erstmal mehrere Sätze aggregieren um die Punktezahl zu bestimmen?

Mal angenommen, es gäbe nur eine Row pro Spieler. Du willst die besten 5 UND deinen eigenen Rang sehen. Mit einem ORDER BY und einem Zähler im PHP müsstest Du alle Sätze der Rangliste einlesen. Sind das viele, kostet das Zeit. Du brauchst aber nur 5 oder 6 davon, je nach eigeneer Platzierung.

Mit RANK kannst Du den Einlesevorgang auf die relevanten Rows beschränken:

SELECT R.ID, R.Name, R.Punkte, R.Rang
FROM (SELECT Name, Punkte, Rank() OVER(ORDER BY punkte DESC) AS Rang
      FROM spielertabelle) R
WHERE R.Rang < 6 OR R.ID = $myId
ORDER BY R.Rang

Fertig. Ob ein nonunique-Index auf die Punkte die Rangbildung ordentlich beschleunigt, weiß ich nicht, das muss ein Explain ergeben.

Natürlich kannst Du auch einen der RANK Workarounds mit einem Zähler verwenden, wie z.B. hier in der ersten Antwort gezeigt:
https://stackoverflow.com/questions/3333665/rank-function-in-mysql

Wenn Du die Punkte erstmal anderweit aggregieren musst, muss man über eine Optimierung nachdenken. Wenn Du das on the fly versuchst, dürfte das bei einer großen Spielertabelle zu lange dauern (wobei man das erstmal ausprobieren und vermessen müsste)

Ich werde also nicht umhin kommen,

unsere Rückfragen zum genaueren Aussehen deiner Daten zu beantworten, um konkrete Hinweise bekommen zu können.

Rolf

[JürgenB]

Hallo @Rolf B,

etwas OT: Stammtisch?

Gruß
Jürgen

[Rolf B]

Hallo JürgenB,

sorry, mein pc muckt und discord startet nicht mehr

ich baue gerade den neuen auf

ETA - wenns so weitergeht wie bisher - 2 wochen 😢

Rolf

[JürgenB]

Hallo Rolf,

ich baue gerade den neuen auf

ETA - wenns so weitergeht wie bisher - 2 wochen 😢

2 Wochen? Musst du die ICs noch einlöten? 😉

Gruß
Jürgen

[Der Martin]

Guten Abend,

ETA - wenns so weitergeht wie bisher - 2 wochen 😢

2 Wochen? Musst du die ICs noch einlöten? 😉

ja, und die Spulen für die Ethernet-Übertrager wickeln. 😉

Davon abgesehen: Fand denn heute irgendwas Erwähnenswertes statt? Ich war leider verhindert (Geburtstagsfeier meiner Mutter), habe das aber nicht vorher kommuniziert. Bin seit etwa einer halben Stunde wieder zuhause.

Einen schönen Tag noch
 Martin

[Andi Reas]

Hallo -

Danke Rolf. Aber krieg das leider einfach nicht zum Laufen.

Okay, habe also eine Datenbank mit der Tabelle bestentabelle, dort habe ich die Spalten id, name und punkte. Mein sql query (Variable $sql) lautet wie folgt:

SELECT id, name, punkte, Rang
FROM (SELECT name, punkte, RANK() OVER(ORDER BY punkte DESC) AS Rang
      FROM bestentabelle) bestentabelle
WHERE Rang < 6 OR id = $myId
ORDER BY Rang

$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_assoc($result)) {
	echo $row["Rang"] . " " . $row["name"] . " " . $row["punkte"];
}

...retourniert:

mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool given

...habe nun überlegt, ob die Datenbank RANK() nicht unterstützt - laut Dokumentation unterstützt MariaDB RANK() aber seit Version 10.2.0. - laut phpmyadmin Dashboard fahre ich mit 10.4.16-MariaDB - also hier eigentlich alles gut.

Ideen?

Wirklich ECHT dankbar, wenn mir da jemand helfen kann!

Andi.

[Rolf B]

Hallo Andi,

wie offenbar 90% der PHP Bastler hältst Du nichts von Fehlerabfragen.

$result = mysqli_query($con, $sql);

if ($result === false) {
   // query ging schief
   echo "query failed: " . mysqli_error($con) . "\n";
}

while($row = mysqli_fetch_assoc($result)) {
	echo $row["Rang"] . " " . $row["name"] . " " . $row["punkte"];
}

Statt einem echo loggt man das normalerweise und zeigt dem User nur, dass ein SQL Fehler passiert ist.

Aber ich habe bereits zwei Vermutungen. Äh nein, eine Gewissheit und eine Vermutung.

Dir fällt sicherlich auf, dass die Query aus zwei geschachtelten SELECTs besteht. Der äußere verwendet den inneren als Datenquelle. Im äußeren SELECT fragst Du nach einer ID. Die liefert der innere aber nicht. Demnach wird gewiss die Meldung kommen, dass er diese Spalte nicht kennt.

Und dann frage ich mich, was Du mit $myId in deiner SQL Variablen gemacht hast. Das war ein Platzhalter für die ID deines Users. Hast Du das an deine Codeverhältnisse angepasst?

Rolf

[Andi Reas]

Ach Gott.

Danke Rolf.

Bin ja (wie nicht allzu schwer ersichtlich) KEIN Backend Entwickler, aber trotzdem schon wieder an dem Punkte angelangt, wo ich das Thema "Web mit Programmieren und so" vielleicht doch ganz lassen sollte.

Wie du natürlich richtig angemerkt hast, schreibt mir die Überprüfung von $result den Fehler der fehlenden ID sogar fein säuberlichst in die Konsole, weitere Fragen daher VOLLKOMMEN überflüssig.

Und dann frage ich mich, was Du mit $myId in deiner SQL Variablen gemacht hast. Das war ein Platzhalter für die ID deines Users. Hast Du das an deine Codeverhältnisse angepasst?

Rolf

Na gut - DANN wäre mir allerdings wohl wirklich nicht mehr zu helfen XD

Dir und allen Mitlesern und deren innen ein schönes Wochenende!

LG, A.R.