✔ [MySQL] Position ermitteln

[Schumiel]

Hallo,

und zwar möchte ich gern eine Art Tabelle aufbauen, die mir sagt, das Datensatz X an 7.Position ist.

Ich habe dafür aktuell folgenden SQL-Befehl:

SELECT count(*) AS Anzahl, user_id FROM list GROUP BY user_id ORDER Anzahl DESC

Durch vergleichen (in PHP) der User-ID kann ich die Abfrage "pausieren", indem ich bei Übereinstimmung der eingeloggten User-ID mit der Ausgabe der SQL-Abfrage ein "break;" einfüge und die Platzierung sozusagen über eine Variable immer um eins addiere.

Die obige DB-Abfrage verbraucht aber viel DB-Last. Kann man das Ganze etwas einfacher stricken?

Auf user_id ist ein Index drauf. Und die Abfrage dauert 1,4 Sekunden.

Und zwar macht jeder User_id einen Eintrag. Umso mehr Einträge, desto eine höhere Position hat er.

Beispiel:
User_id 1 hat 4 Einträge.
User_id 2 hat 5 Einträge.
User_id 3 hat 2 Einträge.
User_id 4 hat 8 Einträge.

Also habe ich insgesamt 19 Datensätze.

Loggt sich User_id 4 ein, muss er da stehen haben, das er die 1.Position inne hat. Für die anderen User_id's eben ihre Positionen. Das mache ich mit der obigen Abfrage, die jedoch viel zu lang dauert.

 

[Yaslaw]

Du kannst auf das sortierte Query eine Zeilennummer hinzufügen
http://wiki.yaslaw.info/wikka/MySqlRowNum

Das sieht dann etwa so aus

SELECT
	@rownum:=@rownum+1 AS rang,
	statistic.*	
FROM
	(SELECT COUNT(user_id) AS anzahl, user_id FROM list GROUP BY userid) AS statistic,
	(SELECT @rownum:=0) AS  vars
ORDER BY statistic.anzahl DESC

Für einen einzelnen User sieht dass dan wie folt aus

SELECT rang
FROM
	(SELECT
		@rownum:=@rownum+1 AS rang,
		statistic.*	
	FROM
		(SELECT COUNT(user_id) AS anzahl, user_id FROM list GROUP BY userid) AS statistic,
		(SELECT @rownum:=0) AS  vars
	ORDER BY statistic.anzahl DESC) AS data
WHERE userid = 12

Natürlich ist der ORDER BY immer eine Bremse. Aber ich seh gerade nciht, wei du diesen umgehen oder mit Indexen beschleunigen könntest

 

[gorefest]

Du solltest den Index mal angeben, damit er angezogen werden kann.

SELECT user_id, count(*) AS Anzahl FROM list USE INDEX (idx_foobar) GROUP BY user_id ORDER Anzahl DESC 

wobei

CREATE INDEX idx_foobar ON list (user_id);

ist

MySQL ist ein bisserl beschränkt, wenns ums finden des richtigen Index geht. Sollte das nicht fruchten, bleibt Dir immer noch Yaslaws Vorschlag mit den Variablen


Grüße
gore

 

[Yaslaw]

Das Problem ist, dass der Index zwar bei einer Selection oder einem Sort auf ein Feld oder mehrere Felder was nützt. Jedoch weiss ich nicht, wie nützlich er ist wenn das Feld zuerst mit einer Group-Funktion verarbeitet wird...

 

[gorefest]

Indexbasiertes Group By geht seit MySQL 5. Tricky ist hier, das das Aggregat des GROUP BY gleichzeitig das Sortierkriterium ist.

Wenn so eine Abfrage aber 1,4 sekunden dauert, würde ich mal auf Filesort ohne Index tippen. Dann sollte der indexhint eigentlich schon Performance bringen.

btw, schau doch mal hier :

http://mysqldba.blogspot.com/2008/06/how-to-pick-indexes-for-order-by-and.html

Das trifft zwar nur bedingt dieses problem, ich fands aber trotzdem ganz nett.

 

[Schumiel]

@yaslwa:
Deine Abfrage dauert nur 0,3 Sekunden. Also Einsparung um ca. 1 Sekunde.

 

[Schumiel]

Nachtrag:
Ich habe den großen Zeitaufwand nun finden können. Ich habe noch eine WHERE-Klausel. Egal, ob ich meine Abfrage nehme oder die von yaslaw.

SELECT count(*) AS Anzahl, user_id FROM list WHERE round = 6 GROUP BY user_id ORDER Anzahl DESC

round hat index mit Kardinalität 3

Sorry, habe die Bedingung voll vergessen.

 

[Yaslaw]

ggf verwendet er dein Index auf user_id, da ich auch den COUNT() auf die user_id gesetzt habe, braucht er kein anderes Feld aus der Tabelle

Hab grad dein Nachtrag gelesen.

1) Mach einen Index über user_id und round
2) Schreibe die WHERE in eine Unterabfrage

SELECT
	@rownum:=@rownum+1 AS rang,
	statistic.*	
FROM
	(
		SELECT 
			COUNT(user_id) AS anzahl, 
			user_id 
		FROM 
			(SELECT user_id FROM list WHERE round = 6) AS myList
		GROUP BY userid
	) AS statistic,
	(SELECT @rownum:=0) AS  vars
ORDER BY statistic.anzahl DESC

 

[Schumiel]

Hm, deine Abfrage dauert nun 1,6 Sekunden.

 

[Yaslaw]

Hast du ein Index auf round?
Wie lange dauert das folgende

SELECT user_id FROM list WHERE round = 6