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:

Insert a new domain, if it doesn't exist already.
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?

What is id for ? Is it just used as the primary key (i.e. it is opaque to your application otherwise ?) — David Cournapeau, Mar 05 '11 at 10:24
Yes. I use it for a request log, and thought varchar would be expansive for a table with lots of rows. — Markus Hedlund, Mar 06 '11 at 16:16

score 1 · Answer 1 · answered Oct 15 '13 at 14:21

1

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

answered Oct 15 '13 at 14:21

Ashrith

655
8
20

Please see this question http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – Markus Hedlund Oct 15 '13 at 16:01
The performance of insert ... on duplicate key update is subjecive. Please read: http://mikefenwick.com/blog/insert-into-database-or-return-id-of-duplicate-row-in-mysql/ – Ashrith Oct 17 '13 at 05:09

score 1 · Accepted Answer · answered Jun 01 '11 at 06:49

1

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

answered Jun 01 '11 at 06:49

Markus Hedlund

23,374
22
80
109

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

2 Answers2