Efficient insertion/update of mysql m:n relations

insert-update, many-to-many, mysql, relation

Solution

You need to check whether the relation exists, and then insert/update.

A.- Enclose the an INSERT query in a try block, and, in case of error, make an update query. That would save all the checks when the relation doesn't exists...

B.- Make all INSERT ON DUPLICATE UPDATE. This is gonna do exactly the same as in "A", but you don't need to worry about exceptions.

Definitely your second idea is absolutely wrong.

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

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 like this:

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

Problem

I am developing a HTML5 multiplayer game, where I have a m:n relation between tables "keyword" and "model", like the following image shows: `keyword.id` and `model.id` are auto_increment unsigned int and `keyword.keyword` is an unique index. For the sake of efficiency, I am searching for a way to manage the relation. The trivial way would be: - Check if keyword already exist - If yes: update `timesCount` and `roundCount` from `model_has_keyword` - If no: `insert into keyword` and `insert into model_has_keyword` But with a growing number of users playing simultaneously, I'm afraid that the trivial way will become too slow. So what is the most efficient way to do this? While searching on StackOverflow, I've stumpled upon two ideas, but I think they both don't fit my needs. - `INSERT INTO table ON DUPLICATE KEY UPDATE col=val` If `INSERT INTO` would be processed, I would need to trigger another `INSERT INTO` statement to insert into both tables `keyword` and `model_has_keyword` - `REPLACE` statement: if I replace the record in table keyword, `id` is assigned the next auto-increment value, so that the reference for table `model_has_keyword` is lost. As I'm not an expert, please correct me if I misunderstood something here.

Original source