Effectively insert / update mysql m: n links

Question

I am developing an HTML5 multiplayer game where I have an m: n relationship between the keyword and model tables, as shown in the following figure:

keyword.id and model.id are auto_increment unsigned int and keyword.keyword is a unique index.
For the sake of efficiency, I’m looking for a way to manage the attitude. Trivial way:

• Check if a keyword exists
• If yes: update timesCount and roundCount from model_has_keyword
• If not: insert into keyword and insert into model_has_keyword

But with the growing number of users playing at the same time, I am afraid that the trivial path will become too slow. So what is the most efficient way to do this?

While searching StackOverflow, I thought about two ideas, but I think that both of them do not meet my needs.

• INSERT INTO table ON DUPLICATE KEY UPDATE col=val
  If the INSERT INTO is processed, I will need to call another INSERT INTO to insert keyword and model_has_keyword into both tables
• REPLACE : if I replace the entry in the keyword of the table, id assigned the next value of automatic growth, so the link for the model_has_keyword table is lost.

Since I am not an expert, please correct me if I do not understand something.

Answer 1

You need to check if the connection exists and then insert / update.

A. - Close the INSERT request in the try block and, in case of an error, create an update request. This will save all checks when the relationship does not exist ...

B. - Do all INSERT ON DUPLICATE UPDATE. This will do the same as in "A", but you do not need to worry about exceptions.

Definitely your second idea is completely wrong.

I would not create table keywords, since you only need the keyword itself ... then I would define my model_has_keyword as follows:

 CREATE TABLE model_has_keyword ( model_id INT NOT NULL, keyword VARCHAR(50) NOT NULL, timesCount INT NOT NULL, roundCount INT NOT NULL, PRIMARY KEY (model_id,keyword) ); 

and update it as follows:

 INSERT INTO model_has_keyword (model_id,keyword,timesCount,$myIntValue) VALUES ($model_id,$keyword,0,0) ON DUPLICATE KEY UPDATE timesCount=timesCount+1,roundCount=roundCount + $myIntValue