Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

mysql - 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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...