Should every MySQL table have an auto-incremented primary key?

Question

• I understand the value of primary keys.
• I understand the value of indexes.

Should every MySQL table have an auto-incremented primary key (ideally with INT field type)?

Update

@Raj More's answer seems most efficient. The issue when I think about it, however, is how this auto-incremented primary key ID will relate to other tables. For example:

table 1

ID  |   firstname  |   lastname | email
----------------------------------------
1   |    john      |   doe      | [email protected]
2   |    sarah     |   stow     | [email protected]
3   |    mike      |   bro      | [email protected]

table 2

ID  | memberid |    display      |    address
--------------------------------------------
1   |    1     | funtime zone    |   123 street
2   |    3     | silly place llc |  944 villa dr 

In the example above, a consumer may come to the site and choose to register for a free product/service. If the consumer chooses, they are able to give additional information (stored in table 2) for additional mailing, etc. The problem I see is in how these tables relate to the 'primary key auto-incremented field'. In table 2, the 'memberid' relates to table 1's ID but this is not 'extremely' clear. Any new information placed into table 2 will increment by 1 whereas not all consumers will choose to participate in the table 2 required data.

Answer 1

I am not a huge fan of surrogate keys. I have yet to see a scenario where I would prefer to use one for every table of a database.

I would say No.

Read up on this answer: surrogate-vs-natural-business-keys

---

The above may be seen as sarcastic or flaming (despite the surprisingly many upvotes) so it's deleted.

In the general case, there have been many questions and answers on surrogate and natural keys so I felt this question is more like a duplicate. My view is that surrogate keys are fine and very useful, mainly because natural keys can lead to very big primary keys in the low end of a chain of connected tables - and this is not handled well by many RDBMS, clustered indexes get big, etc. But saying that "every MySQL table should have an auto-incremented primary key" is a very absolute statement and I think there are cases when they really offer little or nothing.

Since the OP updated the question, I'll try to comment on that specific topic.

I think this is exactly a case where an autoincrementing primary key is not only useless but adds negative value. Supposing that table1 and table2 are in 1:1 relationship, the memberid can be both the Primary Key and a Foreign Key to table1.

Adding an autoincrementing id column adds one index and if it's a clustered one (like InnoDB PK indexes) increases the size of the memberid index. Even more, if you have such an auto-incrementing id, some JOIN of table2 to other tables will have to be done using this id (the JOINs to tables in 1:n relation to table2) and some using memberid (the JOINs to tables in 1:n relation to table1). If you only have memberid both these types of JOINs can be done using memberid.

Answer 2

I am a huge fan of surrogate keys. I have yet to see a scenario where I would prefer not use one.

I would say Yes.

Read up on this answer Surrogate vs. natural/business keys

Edit

I will change my answer to include the following:

There are certain scenarios that I now use the actual value as a surrogate key:

DimDate (20151031, 20151101, 20151102....) DimZipCode (10001, 10002, 10003...)

Everything else gets Surrogate Keys.