CODEWITHSUNDEEP
mysql
melih
melih

Reputation: 380

Which DB design is faster: a unique index and INSERT IGNORE, or using SELECT to find existing records?

I have a table with just one column: userid.

When a user accesses a certain page, his userid is being inserted to the table. Userids are unique, so there shouldn't be two of the same userids in that table.

I'm considering two designs:

  1. Making the column unique and using INSERT commands every time a user accesses that page.
  2. Checking if the user is already recorded in the table by SELECTing from the table, then INSERTing if no record is found.

Which one is faster?

Upvotes: 2

Views: 694

Answers (6)

awe
awe

Reputation: 22452

You should make it unique in any cases.

Wether to check first using SELECT, depends on what scenario is most common. If you have new users all the time, and only occationally existing users, it might be overall faster for the system to just insert and catch the exception in the rare occations this happens, but exception is slower than check first and then insert, so if it is a common scenario that it is an existing user, you should allways check first with select.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425533

Definitely create a UNIQUE index, or, better, make this column a PRIMARY KEY.

You need an index to make your checks fast anyway.

Why don't make this index UNIQUE so that you have another fallback option (if you for some reason forgot to check with SELECT)?

If your table is InnoDB, it will have a PRIMARY KEY anyway, since all InnoDB tables are index-organized by design.

In case you didn't declare a PRIMARY KEY in your table, InnoDB will create a hidden column to be a primary key, thus making your table twise as large and you will not have an index on your column.

Creating a PRIMARY KEY on your column is a win-win.

You can issue

INSERT
IGNORE
INTO    mytable
VALUES  (userid)

and check how many records were affected.

If 0, there was a key violation, but no exception.

Upvotes: 4

André Hoffmann
André Hoffmann

Reputation: 3553

How about using REPLACE?

If a user already exists it's being replaced, if it doesn't a new row is inserted.

Upvotes: 2

Question Mark
Question Mark

Reputation: 3606

orrrrrrr 

INSERT INTO xxx (`userid`) VALUES (4) ON DUPLICATE KEY UPDATE userid=VALUE(`userid`)

Upvotes: 0

Svetlozar Angelov
Svetlozar Angelov

Reputation: 21670

SELECT is faster... but you'd prefer SELECT check not because of this, but to escape from rasing an error..

Upvotes: 1

dusoft
dusoft

Reputation: 11469

what about doing update, e.g. 

UPDATE xxx SET x=x+1 WHERE userid=y

and if that fails (e.g. no matched rows), then do an insert for a new user?

Upvotes: 1

Related Questions