best practice for maintaining ranking tables?

Question

Using MySQL and PHP, I have a simple ranking table with 2 columns user and score.

Once a week, I re-run the ranking script and it computes scores for each user.

• many users have new scores
• some do not
• some times there are new users to add to the table

What's the best way to approach this with MySQL? Does update work if I need to add new rows? Would insert override existing rows such that I could just insert the entire table again? Would it be best to just drop the table and re-write it from scratch?

**Sample Data**

User01   2500
User02   3000
User03    100

**New Data to be Added**

User01   2700
User02   4000
User04   1000 // new account

Except with thousands of users...

Answer 1

INSERT will not overwrite existing rows, but instead yield an error (if, for example, you marked the user column as unique and try to add an already existing user). Similarly, UPDATE will not insert new rows, only change existing ones.

Dropping and rewriting the whole table seems like an acceptable solution, if most users need updating and there are users that should be dropped, i.e. not be contained in the new score tables any longer.

Otherwise, I would update the table:

The portable solution is to check whether a key (i.e. user) is already contained in the table (via SELECT) and use then either INSERT for non-existing keys or UPDATE for already existing keys. Of course, you could also just try INSERT and check the error code, dropping back to UPDATE, if you get an constraint violation error.

On MySQL, you can also use the following syntax which allows you the operation with only a single query:

INSERT INTO ... ON DUPLICATE KEY UPDATE score = ...

However, this is not portable and will not work with other RDBMS, only with MySQL.

Answer 2

As you mention two way

UPDATE OR INSERT

I think INSERT would be good over update. For insert you add one query. Where as for update you have to run 2 query for each user. first select user and then update based on userId.

So fastest would be insert and you can sum it any time using sql query.

Answer 3

The REPLACE ... INTO syntax will insert new rows when needed and delete + insert for existing rows:

# deletes existing and inserts new row:
REPLACE INTO users SET score = 2000, username = 'User01';
# deletes existing and inserts new row
REPLACE INTO users SET score = 3000, username = 'User02';
# inserts new row
REPLACE INTO users SET score = 4000, username = 'User04';  

http://dev.mysql.com/doc/refman/5.0/en/replace.html

My favorite method though is to use the ON DUPLICATE KEY syntax - mainly because I've always assumed that deleting a row then re-inserting it would be more 'expensive' then just updating an existing row. Assuming that the username field is a UNIQUE or PRIMARY KEY index you can do the following:

# Updates existing User01 row
INSERT INTO users SET score = 2000, username = 'User01' ON DUPLICATE KEY UPDATE score = 2000;
# Updates existing User02 row
INSERT INTO users SET score = 3000, username = 'User02' ON DUPLICATE KEY UPDATE score = 2000;
# Inserts new User04 row
INSERT INTO users SET score = 4000, username = 'User04' ON DUPLICATE KEY UPDATE score = 2000;

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html