Clever Geek Handbook

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

I have a table with one column: userid.

When a user visits a specific page, their user ID is inserted into the table. Users are unique, so there should not be two identical user IDs in this table.

I am considering two projects:

  • Create a unique column and use commands INSERT

    every time the user accesses this page.
  • Checking that the user is already recorded in the table by SELECT

    ing from the table, then INSERT

    ing if no record is found.

Which one is faster?

+2


source to share


6 answers


Definitely create an index UNIQUE

, or better, make this column a PRIMARY KEY

.

You need an index to keep your checks fast.

Why not make this index UNIQUE

so that you have a different fallback option (if you forgot to check with for some reason SELECT

)?

If yours is a table InnoDB

, it will anyway PRIMARY KEY

, since all tables are InnoDB

organized by design index.

If you have not declared PRIMARY KEY

in your table, InnoDB

will create a hidden column that will become the primary key, which will make your table twiz so big and you will not have a pointer to your column.



Creating PRIMARY KEY

in your column is a win-win.

You can specify

INSERT
IGNORE
INTO    mytable
VALUES  (userid)

and check how many records were affected.

If 0

, there was a key violation, but not an exception.

+3


source


How about using REPLACE ?



If the user already exists, it is replaced if no new row is inserted.

+2


source


how about doing an update, eg.

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

and if that doesn't work (for example no matching rows), will you do the insert for the new user?

+1


source


SELECT is faster ... but you'd rather check the SELECT because of this, but to avoid an extension error.

+1


source


orrrrrrr

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


source


You must make it unique in all cases.

Wether to check SELECT first depends on which scenario is the most common. If you have new users all the time and only existing users, it might be generally faster if the system would just insert and catch the exception on rare occasions it does, but the exception is slower than checking first and then inserting, so if the normal scenario is an existing user, you should always check it first.

0


source






More articles:


All Articles