1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen

[MySQL] Position ermitteln

Dieses Thema im Forum "Relationale Datenbanksysteme" wurde erstellt von Schumiel, 27. April 2010.

  1. Schumiel

    Schumiel Erfahrenes Mitglied

    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:

    PHP:
    1. 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. :(
  2. Yaslaw

    Yaslaw n/a Moderator

    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. SELECT
    2.     @rownum:=@rownum+1 AS rang,
    3.     statistic.*
    4. FROM
    5.     (SELECT COUNT(user_id) AS anzahl, user_id FROM list GROUP BY userid) AS statistic,
    6.     (SELECT @rownum:=0) AS  vars
    7. ORDER BY statistic.anzahl DESC
    Für einen einzelnen User sieht dass dan wie folt aus
    Code (SQL):
    1. SELECT rang
    2. FROM
    3.     (SELECT
    4.         @rownum:=@rownum+1 AS rang,
    5.         statistic.*
    6.     FROM
    7.         (SELECT COUNT(user_id) AS anzahl, user_id FROM list GROUP BY userid) AS statistic,
    8.         (SELECT @rownum:=0) AS  vars
    9.     ORDER BY statistic.anzahl DESC) AS DATA
    10. 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
    Zuletzt von einem Moderator bearbeitet: 23. Mai 2014
    Sven Mintel sagt Danke.
  3. gorefest

    gorefest Erfahrenes Mitglied

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

    Code (SQL):
    1.  
    2. SELECT user_id, COUNT(*) AS Anzahl FROM list USE INDEX (idx_foobar) GROUP BY user_id ORDER Anzahl DESC
    3.  
    4. wobei
    5.  
    6. CREATE INDEX idx_foobar ON list (user_id);
    7.  
    8. ist
    9.  
    10.  
    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
    Zuletzt von einem Moderator bearbeitet: 23. Mai 2014
  4. Yaslaw

    Yaslaw n/a Moderator

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

    gorefest Erfahrenes Mitglied

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

    Schumiel Erfahrenes Mitglied

    @yaslwa:
    Deine Abfrage dauert nur 0,3 Sekunden. Also Einsparung um ca. 1 Sekunde.
    Zuletzt bearbeitet: 27. April 2010
  7. Schumiel

    Schumiel Erfahrenes Mitglied

    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.

    Code (PHP):
    1. 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. :(
  8. Yaslaw

    Yaslaw n/a Moderator

    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. SELECT
    2.     @rownum:=@rownum+1 AS rang,
    3.     statistic.*
    4. FROM
    5.     (
    6.         SELECT
    7.             COUNT(user_id) AS anzahl,
    8.             user_id
    9.         FROM
    10.             (SELECT user_id FROM list WHERE round = 6) AS myList
    11.         GROUP BY userid
    12.     ) AS statistic,
    13.     (SELECT @rownum:=0) AS  vars
    14. ORDER BY statistic.anzahl DESC
    Zuletzt von einem Moderator bearbeitet: 23. Mai 2014
    Schumiel sagt Danke.
  9. Schumiel

    Schumiel Erfahrenes Mitglied

    Hm, deine Abfrage dauert nun 1,6 Sekunden.
  10. Yaslaw

    Yaslaw n/a Moderator

    Hast du ein Index auf round?
    Wie lange dauert das folgende
    Code (SQL):
    1. SELECT user_id FROM list WHERE round = 6
  11. Schumiel

    Schumiel Erfahrenes Mitglied

    "die Abfrage dauerte 0.0001 sek"
  12. gorefest

    gorefest Erfahrenes Mitglied

    Code (PHP):
    1. 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  
  13. gorefest

    gorefest Erfahrenes Mitglied

    aso, und poste doch mal nen EXPLAIN
  14. Schumiel

    Schumiel Erfahrenes Mitglied

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

    Schumiel Erfahrenes Mitglied

    EXPLAIN:

  16. gorefest

    gorefest Erfahrenes Mitglied

    nochwas, zähle doch mal nicht nach * sondern nach user_id

    je mehr spaltem im selekt, desto teurer die query
  17. gorefest

    gorefest Erfahrenes Mitglied

    Hi,

    versuch doch mal das USE INDEX. das fehlt in Deinem Explain

    Code (SQL):
    1.  
    2. SELECT user_id, COUNT(user_id) AS Anzahl
    3. FROM list
    4. USE INDEX (foobar)
    5. WHERE round = 6
    6. GROUP BY user_id ORDER Anzahl DESC
    7.  
  18. Schumiel

    Schumiel Erfahrenes Mitglied

    Ob das USE INDEX dabei ist oder nicht, auch ob * oder user_id, das EXPLAIN-Ergebnis ist 100% identisch.
  19. gorefest

    gorefest Erfahrenes Mitglied

    OK, finalversuch

    INDEX :

    Code (SQL):
    1.  
    2. CREATE INDEX idx_foobar ON list(round, user_id);
    3.  
    QUERY I:

    Code (SQL):
    1.  
    2. SELECT user_id, COUNT(user_id) AS Anzahl
    3. FROM list
    4. USE INDEX ( idx_foobar )
    5. WHERE round = 6
    6. GROUP BY round, user_id
    7. ORDER Anzahl DESC
    8.  

    QUERY II:

    Code (SQL):
    1.  
    2. SELECT user_id, anzahl
    3. FROM (
    4.    SELECT user_id, COUNT(user_id) AS Anzahl
    5.    FROM list
    6.    USE INDEX ( idx_foobar )
    7.    WHERE round = 6
    8.    GROUP BY round, user_id )X
    9. oder BY X.anzahl DESC
    10.  
    mach doch dafür mal bitte einen EXPLAIN.

    [EDIT]
    versuch auch mal ruhig FORCE INDEX statt USE INDEX
    [/EDIT]
    Zuletzt bearbeitet: 27. April 2010
    Schumiel sagt Danke.
  20. Schumiel

    Schumiel Erfahrenes Mitglied

    *freu* Beide schaffen es auf 0,4 Sekunden und das nur selten.

    Code (Text):
    1. die Abfrage dauerte 0.0001 sek
    ist erreicht.

Diese Seite empfehlen