Equinox04 Equinox04 - 5 months ago 6
SQL Question

What's the fastest way to get the record of a row of data?

So I was having a test around with a large database table of users that has just over 1 million rows of data, attempting to come up with some kind of "user rank" but it seems very slow in comparison to any other way I use this data so I was wondering if I was going about it the wrong way or not.
I'm pulling every row of data with the two columns

id
and
points
, grouping by points so people with the same points will be the same rank and then ordering in descending order.

Here's a quick throw together I did to test it 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.';
}
}


points
and
id
are indexed

This seemed to load quite slowly, not 'really' slow but slower than I'd like so I decided to do some testing with it and check how long the script was taking to execute using the following method:

<?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.';


This averaged at 1.9468239237 seconds to load based on 1000 loads.

I then added a
break;
in the loop when it finds your rank thinking maybe it would shorten the processing time but to no avail.

So my question is, is there a better way you calculate something like this?

Answer

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
Comments