0

I am running a script that run the following code for all of the users and update the MySQL database. Currently, it updates more than 400.000 times, something that it is huge for the processing power of my server.

while($usersResult = mysql_fetch_assoc($get_users)) {   

    $percentile_rank = $percentile_rank * $user_variable / 100);
    mysql_query("UPDATE new_table SET percentile_visits_frequency='$percentile_rank' WHERE hash='$usersResult[hash]'");

}

I wonder if there is a way to edit the code, in order to update 1000, 2000 or 10.000 hash with one query, instead 1 query for each hash.

Thank you

EnexoOnoma
  • 8,454
  • 18
  • 94
  • 179
  • 3
    ***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard Jan 16 '17 at 21:12
  • 2
    [Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! – Jay Blanchard Jan 16 '17 at 21:12
  • Since each percentile is unique to the hash you would have to run a query for each, unless I misunderstand your purpose. – Jay Blanchard Jan 16 '17 at 21:13
  • @JayBlanchard true about PDO. Also, the perentile_rank is unique for each hash – EnexoOnoma Jan 16 '17 at 21:17
  • Then it would seem you have no choice. [Read this](http://stackoverflow.com/questions/16977898/mysql-optimizing-insert-speed-being-slowed-down-because-of-indices) for some helpful tips. – Jay Blanchard Jan 16 '17 at 21:19
  • You could make an `update` statement with a `select`, like displayed in [this question](http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query). The select query can be range of simple `select $percentile_rank as percentile_rank , $user_variable as user_variable` which are *union alled* together. You could do that with parameters too. Just add question marks for the variables and bind the values. Anyway. It's a tad more complicated, but worth it in terms of performance. – GolezTrol Jan 16 '17 at 21:33
  • @JayBlanchard I put a thought on it, and I can group similar `percentile_rank` – EnexoOnoma Jan 16 '17 at 21:36
  • You have a query which is creating the `$get_users`. Then you have `percentile_rank` and `$user_variable` variables. I do not know the relation between these two variables and `$get_users`. But if they have any relation, maybe you can create a single query which updates all the records you have in `$get_users` with the calculation of `percentile_rank` inside the query itself – EhsanT Jan 17 '17 at 01:00
  • Without knowing the initial query that populates `$usersResult`, the potential values of `$user_variable`, or even the barest description of what any of this *does* it's nigh-impossible to answer your question. – Sammitch Jan 17 '17 at 01:19

0 Answers0