0

I have database with 1 mio records that shows user points for each project. More points user has higher is his rank.

Table user_points
user_id | points | project_id | rank
1       | 2434   | 1          | 1
2       | 43     | 2          | 3
...

Now I want to update rank every 24 hours based on users points for each project so it won't show just how many points user has but also what is his rank based on how many points other users have. To calculate users rank I made this code:

$stmt = $this->conn->prepare("SELECT * FROM user_points WHERE project_id=1");
$stmt->execute();
$users = $stmt->fetchAll(); 
foreach($users as $user){
  $sql = "SELECT COUNT(user_id) FROM `user_points` WHERE project_id=1 AND points<".$user['points']
  $stmt = $this->conn->prepare($sql);
  $rank = $stmt->fetchColumn();
  $sql2 = "UPDATE user_points SET rank=$rank WHERE project_id = 1 AND user_id=".$user['user_id'];
  ...
}

Obviously this is very slow. I tested it with wamp and 1 mio records and it updated only 300 records per minute. What technology or method I could use for such scenario?

tadman
  • 208,517
  • 23
  • 234
  • 262
user1324762
  • 765
  • 4
  • 7
  • 24
  • 2
    Are you using MySQL or MS SQL Server? (Remove the unrelated tag.) – jarlh Apr 11 '23 at 20:43
  • Guess what? MySQL is a totally different product to SQL Server - do yourself a favour and correct your tags so the right experts look at your post. – Dale K Apr 11 '23 at 20:45
  • 2
    Wouldn't be better if you create a view rather than doing all those updates ? – Ergest Basha Apr 11 '23 at 20:52
  • 2
    **WARNING**: This has some severe [SQL injection bugs](http://bobby-tables.com/) because user data is used inside the query. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. **NEVER** put `$_POST`, `$_GET` or any user data directly in your query. – tadman Apr 11 '23 at 21:00
  • 3
    You can do an `UPDATE` based on a `SELECT`. You do not need to go through this agony of fetching a million rows and running two million queries. – tadman Apr 11 '23 at 21:01
  • 1
    And if you're using MySQL 8.x you can use window functions in the UPDATE. – Barmar Apr 11 '23 at 21:02
  • tadman: "because user data is used inside the query" I don't see it here. – Wiimm Apr 11 '23 at 21:58

0 Answers0