MySQL - If exists, get primary key. Else, add entry

Question

My table has two columns: "id" (Auto Increment, Primary) and "number" (Unique). Now I want to the following:

• if the number already exists, return the id;
• else, add entry to the table and return its id.

What's the most efficient method to do this job?

Note:

• There is a greater probability that the number is new;
• The table will contain hundreds of thousands of records.

Thank you!

Answer 1

INSERT IGNORE INTO table (number) VALUES (42);

SELECT id FROM table WHERE number = 42;

That's probably the most efficient in MySQL. You could use a Stored Procedure to lump them up, which may or may not be slightly more efficient.

EDIT:

If you think it's going to be rare that new numbers come up, this will be even faster:

SELECT id FROM table WHERE number = 42;

if (!id) {

  INSERT INTO table WHERE number = 42;
  id = SELECT @LAST_INSERT_ID;

}

There is a possible race condition here if concurrent threads simultaneously select then insert the same number at the same time. In this case, the later insert will fail. You could recover from this by re-selecting on this error condition.