[Resolved] Speed

[________]

I got a table with about 500.000 rows that holds info about users. If an admin open the info about a user, the field status is set to "2".

The problem is when an admin exit the page with the back button, because then the status is not set to "1" again. When a nother admin then search the users table, the status will look like an admin is looking at the info - but that is incorrect.

So I have build a script that sets the status back to 1 if the timestamp fild is older then 10 minuts. Now it works like this:
while($row = mysql_fetch_array($result)){
if ($row['status'] == '2' and $row['timestamp'] < $now){
$update = mysql_query("UPDATE useres SET status = '1' where id = '".$row[id]."' ");
}
....
}

If there are many rows where status = 2 the script will run a lot of querys.

Is it faster to run a query at the start of the script that sets the status to 1 on all rows at the beginning of the script?

[Nate]

You should be able to cut that down to one query if you use DATETIME for your timestamp in mysql.

ex. mysql_query("UPDATE useres SET status = '1' WHERE status=2 AND logged_date_time>=SUBTIME(NOW(),'00:10:00')");

[________]

So it is faster to update all the fields in one query?

The way it is now it only updates about 50 rows at the time. Will it take long to update all 500.000 rows?

[Nate]

SELECT logged_date_time > CONCAT(CURDATE(),' ',SEC_TO_TIME(TIME_TO_SEC(NOW())+20*60))

something like that should work in versions before mysql 4.1.1

[Nate]

It will be much faster to update all rows in one query than to update 1 row per query and looping through a bunch of queries.

[________]

Thanks!