What is the fastest way to get a record of a series of data?

Question

So, I had a test around with a large user database table that has just over 1 million rows of data, trying to come up with some kind of “user rank”, but it seems very slow compared to any other way I use this data, so I was wondering if I was wrong about this or not. I pull each row of data with two columns idand pointsgrouping by points, so people with the same points will be of the same rank, and then sorted in descending order.

Here I quickly worked to check this out:

<?php
session_start();
$rank = 0;
$query = $conn->prepare("SELECT id, points FROM users GROUP BY points ORDER BY points DESC");
$query->execute();
foreach($query as $result){
    $rank += 1;
    if($result['id'] == $_SESSION['myid']){
        echo '' . $_SESSION['myuser'] . ' is rank ' . number_format($rank) . ' globally.';
    }
}

pointsand idindexed

, , "" , , , , script, :

<?php
$starttime = microtime(true);
session_start();
$rank = 0;
$query = $conn->prepare("SELECT id, points FROM users GROUP BY points ORDER BY points DESC");
$query->execute();
foreach($query as $result){
    $rank += 1;
    if($result['id'] == $_SESSION['myid']){
        echo '' . $_SESSION['myuser'] . ' is rank ' . number_format($rank) . ' globally.';
    }
}
$endtime = microtime(true);
$duration = $endtime - $starttime;
echo '<br /><br />This page took ' . $duration . ' seconds to load.';

1,9468239237 1000 .

break; , , , , .

, : - ?

Answer 1

http://sqlfiddle.com/#!9/05189/3

SELECT t.*
FROM users u
INNER JOIN (
  SELECT id, points, IF(@rank IS NULL,@rank:=1,@rank:=@rank+1) rank
  FROM users 
  ORDER BY points DESC) t
ON u.id = t.id
WHERE u.id = 3; # <- 3 is id you are looking for

Answer 2

:

SELECT id, Name, 1+(SELECT count(*) from users u1 WHERE u1.Points > u1.Points) as Rank, Points
FROM users u WHERE u.id = your_id;

Answer 3

-

SELECT users.id, ranks.rank
FROM users
INNER JOIN (
    SELECT points, ROW_NUMBER() AS rank
    FROM users
    GROUP BY points
    ORDER BY points DESC
) ranks ON users.points = ranks.points
WHERE users.id = ?

.

: , MySQL ROW_NUMBER(). :

SELECT users.id, ranks.rank
FROM users
INNER JOIN (
    SELECT t1.points, @rank := @rank + 1 AS rank
    FROM (
        SELECT points
        FROM users
        GROUP BY points
        ORDER BY points DESC
    ) t1, (SELECT @rank := 0) t2 
) ranks ON users.points = ranks.points
WHERE users.id = ?