Which one is faster

Question

there is a table with just one column: userid

when users access that page, his userid is being inserted to the table but userid's are unique, so there isn't two same userid's on that table.

i got two choices (i think);

making unique and using insert command everytime a user accesses that page

checking if the user alreade recorded by SELECT then insert if no record found

which one is faster?

Answer 1

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?

Answer 2

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

Answer 3

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.

Answer 4

orrrrrrr

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

Answer 5

How about using REPLACE?

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

Answer 6

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.