mysql update query optimization

Question

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...)?

solution is to measure the limit. 5k entries/5 minutes should be suitable for any sql server. — nothrow
– nothrow, Commented May 6, 2013 at 20:39

NMALKO · Accepted Answer · 2013-05-06 20:55:51Z

1

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.

edited May 6, 2013 at 20:55

answered May 6, 2013 at 20:49

NMALKO

1661 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manu Eidenberger · Accepted Answer · 2013-05-06 20:50:58Z

0

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...

answered May 6, 2013 at 20:50

Manu Eidenberger

2,0961 gold badge18 silver badges24 bronze badges

Comments

bobwienholt · Accepted Answer · 2013-05-06 21:00:47Z

0

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.

answered May 6, 2013 at 21:00

bobwienholt

17.6k3 gold badges43 silver badges48 bronze badges

Collectives™ on Stack Overflow

mysql update query optimization

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related