Very slow php update code after adding some data to the database?

Question

I have a soccer fantasy league php script, there is 20 teams and more than 400 players assigned to the teams, and I have 500 users.

Every week there is a points should be assigned to each player, so that in the end every user will have a total points from his formation and that will generate the ranking for the season.

First week points were added normally, but for the second week point the addpont section became so slow, and for the 3rd week points a socket time out error appears.

here is the code I'm using in adding points to users:

// Adding Point To the user player list
$sql_user="select * from ".$prev."user LIMIT 0, 100 ";  

$re_user=mysql_query($sql_user);  
while($d_user=mysql_fetch_array($re_user))  
{  
$userID=$d_user['id'];  

  $sql_addpointgroup="select * from ".$prev."addpoint group by weekno order by weekno";  
  $re_addpointgroup=mysql_query($sql_addpointgroup);  
  while($d_addpointgroup=mysql_fetch_array($re_addpointgroup))  
  {     
      $points=0;  
      $sql_addpoint="select * from ".$prev."addpoint where   weekno='".$d_addpointgroup['weekno']."'";  
      $re_addpoint=mysql_query($sql_addpoint);  
      while($d_addpoint=mysql_fetch_array($re_addpoint))  
      {  
        $points=$d_addpoint['points'];  
        $sql_weekstatistic="select * from ".$prev."weekstatistic where   weekno='".$d_addpointgroup['weekno']."' and userID='$userID' and playerID='".$d_addpoint['playerID']."'";  
        $re_weekstatistic=mysql_query($sql_weekstatistic);  
        if(mysql_num_rows($re_weekstatistic)>0)  
        {  
            $sql_update="update ".$prev."weekstatistic set points='$points' where weekno='".$d_addpointgroup['weekno']."' and userID='$userID' and playerID='".$d_addpoint['playerID']."'";  

            mysql_query($sql_update);  
        }  
      }  
}     
}

I've limited the number of users to 100 user every submitting and even so the code still slow.

the slowness is only with this code other website sections are working normally.

Is there any way to write the code in other faster way, or if there is any thing else I can do?

many thanks in advance,

FYI, your queries are likely not secure. You are not escaping any of your data for use in a query, and will likely be hacked. Consider using prepared queries with PDO to avoid this problem. — Brad, Aug 26 '12 at 19:07
I would like to add if you don't want to use PDO you can use real_escape_string() to prevent SQL Injection hacking on your script. Here is a good link that shows how to use it. http://php.net/manual/en/mysqli.real-escape-string.php — Ishikawa, Aug 26 '12 at 19:13
please check you queries with explain and check that you're using the right index, no full tables scans and so on. — jfried, Aug 26 '12 at 19:38
Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Aug 26 '12 at 19:42

score 1 · Answer 1 · answered Aug 26 '12 at 19:41

select * from

I hope you know the meaning of * in the SELECT Query. It means ALL COLUMNS. You don't need the values of all the column per a row. Be specific in your query and choose only the columns you need.

For instance, this query:

$sql_weekstatistic="select * from ".$prev."weekstatistic where   weekno='".$d_addpointgroup['weekno']."' and userID='$userID' and playerID='".$d_addpoint['playerID']."'";

You already have the value of:

weekno @ $d_addpointgroup['weekno']
userID @$userID
playerID @$d_addpoint['playerID']

Based on the other queries.

Yet, you still use SELECT * FROM.

This is my small tip about performence and SQL.

BTW , secure your queries , by using mysql_real_escape_tring(), or , and even better , move to mysqli or PDO as @lshikawa is suggesting.

I'm sure that is the case, there is no reason to use `*` unless it's really necessary or your table is very small. Up-Voted. Read more about SQL injections here: http://stackoverflow.com/questions/11939226/sql-injections-and-adodb-library-general-php-website-security-with-examples — Ilia Ross, Aug 26 '12 at 19:44

score 0 · Answer 2 · answered Aug 26 '12 at 20:40

I'm not going to mention the issues with SQL injection other than to recommend you follow the recommendations from others in this thread. Seriously - if you're asking people to submit personal data for storage in your database, you should protect them from having that data stolen.

The reason your process is slow is probably twofold.

Firstly, you're using 5 queries when only one is necessary. You're asking the database for lots of data that you use to ask it more questions - without knowing your schema, it's hard to give you a working replacement, but something like:

update ".$prev."weekstatistic 
set      points   = ap.points 
from     weekstatistic ws, 
         addpoint      ap, 
         user          u
where    weekno   = //work out the current weeknumber 
and      userID   = u.userID 
and      playerID = ap.playerID'

This should achieve the same, but in only one query; that should be much faster.

Secondly, you probably don't have the right indexes on your tables - that's a classic cause for "my query gets slower as I get more data in my table". Read up on EXPLAIN, and add some indices.

Very slow php update code after adding some data to the database?

2 Answers2