Fastest way to insert, if not exist, then get id in MySQL

Question

There's this table.

| id | domain |

id is the primary key. domain is a unique key.

I want to:

1. Insert a new domain, if it doesn't exist already.
2. Get the id for that domain.

Now I'm doing it like this:

INSERT INTO domains
SET domain = 'exemple.com'
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)

Then PDO::lastInsertId() to get the id.

But it's critical that this is as fast as it could, so I though I'd ask: Can I do this in a better way?

Baptiste

The CSS for ol li code seems a bit off? – Baptiste

What is id for ? Is it just used as the primary key (i.e. it is opaque to your application otherwise ?) – Orometer

Yes. I use it for a request log, and thought varchar would be expansive for a table with lots of rows. – Baptiste

Answer 1

Until someone says otherwise, I'm saying No, that's the best way.

Baptiste

Answer 2

This method has a side effect: "The auto increment id increments by one each time the duplicate key is found".
When the query is run with exemple.com as the value first time it creates the entry. Lets say you repeat that query 13 more times. After that you try with xyz.com you will be surprised to see that instead of auto increment id=2 you get 15.

1 exemple.com
15 xyz.com
25 pqr.com
50 thg.com

Nipha

Please see this question #549041 – Baptiste

The performance of insert ... on duplicate key update is subjecive. Please read: mikefenwick.com/blog/… – Nipha