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

Question

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?

Answer 1

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.