CODEWITHSUNDEEP
mysqloptimization
Zbarcea Christian
Zbarcea Christian

Reputation: 9548

mysql update query optimization

I have a database with 200+ entries, and with a cronjob I'm updating the database every 5 minutes. All entries are unique.

My code:

for($players as $pl){
   mysql_query("UPDATE rp_players SET state_one = '".$pl['s_o']."', state_two = '".$pl['s_t']."' WHERE id = '".$pl['id']."' ")
   or die(mysql_error());
}

There are 200+ queries every 5 minute. I don't know what would happen if my database will have much more entries (2000... 5000+). I think the server will die.

Is there any solution (optimization or something...)?

Upvotes: 0

Views: 88

Answers (3)

bobwienholt
bobwienholt

Reputation: 17610

I would load all data that is to be updated into a temporary table using the LOAD DATA INFILE command: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

Then, you could update everything with one query:

UPDATE FROM rp_players p
INNER JOIN tmp_players t
  ON p.id = t.id
SET p.state_one = t.state_one
  , p.state_two = t.state_two
;

This would be much more efficient because you would remove a lot of the back and forth to the server that you are incurring by running a separate query every time through a php loop.

Depending on where the data is coming from, you might be able to remove PHP from this process entirely.

Upvotes: 0

NMALKO
NMALKO

Reputation: 166

I think you can't do much but make the cron to be executed every 10 minutes if it's getting slower and slower. Also, you can set X rule to delete X days old entries.

Upvotes: 1

Manu Eidenberger
Manu Eidenberger

Reputation: 2096

If id is your primary (and unique as you mentioned) key, updates should be fast and couldn't be optimised (since it's a primary key... if not, see if you can add an index).

The only problem which could occur (on my mind) is cronjob overlapping, due to slow updates: let's assume your job starts at 1:00am and isn't finished at 1:05am... this will mean that your queries will pile up, creating server load, slow response time, etc...

If this is your case, you should use rabbitmq in order to queue your update queries in order to process them in a more controlled way...

Upvotes: 0

Related Questions