There is a script that opens the entire user table, then sorts it by field and gives the number in the rating:

function num_tasks_rate($a) { $query = mysql_query("SELECT `vk_id` FROM `users` WHERE `ban` != 2 ORDER BY `tasks_summ_done` DESC"); for($i = 1; $i <= $data_rates = mysql_num_rows($query); $i++) { $data_rates = mysql_fetch_array($query); if($data_rates['vk_id'] == $a) { $n = $i; break; } } return $n; }

But such a code loads the system very much, apparently because it opens 20,000 users. How can you optimize or write differently?

    2 answers 2

    Try this:

     select count(u.vk_id)+1 from users u join (select tasks_summ_done from users where vk_id=$a) s on u.tasks_summ_done>s.tasks_summ_done and u.ban <> 2

    There are two indices for this query:

    • across the field vk_id
    • across the tasks_summ_done field.

      So, in the forehead, it seems not to solve. The easiest way is to denormalize the database and add the "rating" field. The alternative is to calculate how many users there are who have tasks_summ_done more than the required user. Something like this (pseudocode):

       $query = mysql_query("SELECT `tasks_summ_done` FROM `users` WHERE `vk_id` = " . (int) $a); $query = mysql_query("SELECT COUNT(*) as `count` FROM `users` WHERE `ban` != 2 AND `tasks_summ_done` > " . (int) $tasks_summ_done_current);

      The only caveat is incorrect processing of the user rating with the same tasks_summ_done - but this can be added as an additional condition to the last query, something like this:

       $query = mysql_query("SELECT COUNT(*) as `count` FROM `users` WHERE `ban` != 2 AND ( `tasks_summ_done` > " . (int) $tasks_summ_done_current . " OR (`tasks_summ_done` = " . (int) $tasks_summ_done_current . " AND `vk_id` > " . (int) $a . ")) ");