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

Das sieht dann etwa so aus

Code sql:

1 2 3 4 5 6 7

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

Code sql:

1 2 3 4 5 6 7 8 9 10

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

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

Code sql:

1 2 3 4 5 6 7

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

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...

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...er-by-and.html

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

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

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.

PHP-Code:

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.

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

Code sql:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

Hm, deine Abfrage dauert nun 1,6 Sekunden.

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

Code sql:

1	SELECT user_id FROM list WHERE round = 6

"die Abfrage dauerte 0.0001 sek"

Zitat von Schumiel

round hat index mit Kardinalität 3

PHP-Code:

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


Bau doch mal folgenden Index auf

CREATE INDEX foobar ON list (round, user_id)

Code sql:

1	SELECT user_id, COUNT(*) AS Anzahl FROM list USE INDEX (foobar) WHERE round = 6 GROUP BY user_id ORDER Anzahl DESC

aso, und poste doch mal nen EXPLAIN

@gorefest:
Leider keine Änderung. Auch immer wieder 1,4 Sekunden, plus/minus kleinere Abweichungen.

EXPLAIN:

[QUOTE=gorefest;1861045]

PHP-Code:

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


Code :

1 2	id       select_type     table       type    possible_keys       key     key_len     ref     rows    Extra 1   SIMPLE  list    ref     runde   runde   4   const   474140  Using where; Using temporary; Using filesort