Numerowanie listy zwracanych rekordów w MySQL

Przy różnorakich konkursach często zachodzi potrzeba wyciągnięcia pozycji konkretnego użytkownika (wpisu w bazie) na tle innych użytkowników (wpisów) w ograniczeniu czasowym.

Czyli np. mamy 10 tys wyników gry z danego dnia i chcemy poznać pozycje w rankingu, które zajmują użytkownicy o identyfikatorach 1, 2, 3.

Oczywistym rozwiązaniem jest zwykły selekt z ORDER BY po polu z punktami i następnie iterowanie w php, jednak gdy liczba danych będzie większa taki SELECT nie wykona się lub będzie bardzo niewydajny.

Rozwiązaniem dużo lepszym jest użycie podzapytania i wykonania obliczeń numerowania na samej bazie a następnie zwrócenie do php jedynie rekordów pasujących do naszych użytkowników.

SET @row = 0; 
SELECT row, user_id, name, correct FROM 
   (SELECT @row:=@row+1 AS row, QR.user_id, QR.name, QR.score 
    FROM quiz_results AS QR 
    WHERE QR.data = '2011-11-11' 
    ORDER BY QR.correct desc) AS sub 
WHERE user_id IN (1, 2, 3)

Na początku inicjujemy zmienną pomocniczą @row, następnie w zapytaniu wewnętrznym „sub” inkrementujemy zmienną pomocniczą dla każdego rekordu, który zwróci baza, wybieramy też te kolumny, które chcemy przekazać do skryptu. Zapytanie zewnętrzne ogranicza listę wyników tylko do tych, które pasują do pożądanych identyfikatorów użytkowników, w kolumnie row dostaniemy pozycję użytkownika.