I am using myqsl an sql server for quite long time. I am C# developer. I always used to create for each table first column Id as primary key and auto-incremented. Last time i saw my colleague (also C# developer) are not using it at all - no id at all. I found lot of issues when there is no id with pk and ai - hard to delete records etc... Our last conversation was also about performance. He said for performance is better to not use Id with auto increment pk for big data. I would like to get your feedback what's the fact?
Let's categorize things into 3 buckets:
Case 1: No natural PK. Then do have an AI. Soner or later, you will find that having it makes maintenance easier. Example: Deleting or modifying a specific row due to a data error. That is, do not depend on the hidden PK that InnoDB will generate.
Case 2: There is a natural PK and you need an index by that column(s). Bingo! A PK is an index, so you can get the performance benefit of the index inherent in a PK. Plus you avoid the space, clutter, etc, of the AI.
Case 3: Natural PK possible, but indexing it is not needed. Now we are in a gray area. Flip a coin. Or, since a
VARCHAR is slightly slower for indexing, pick the
Case 4: (Yeah, I started with 3 buckets.) If the table is
PARTITIONed, a lot of additional considerations come into play. I won't go into them in this Q&A.
Let me provide an example of Case 2 -- a many-to-many mapping table.
A lot of people build essentially
CREATE TABLE A_to_B_mapping id INT AUTO_INCREMENT, -- not used id_a, -- link to table A id_b, -- link to table B PRIMARY KEY(id), INDEX(id_a), -- for joining from `A` to `B` INDEX(id_b) -- if going the other way )
But this would be much better:
CREATE TABLE A_to_B_mapping -- no AUTO_INCREMENT, -- saving space id_a, -- link to table A id_b, -- link to table B PRIMARY KEY(id_a, id_b), -- PK, covering, unique, and for joining from `A` to `B` INDEX(id_b, id_a) -- if going the other way, now 'covering' ) ENGINE=InnoDB; -- so PK is 'clustered'
A "Covering" index is an
INDEX that has all the columns needed anywhere in the
SELECT. A non-covering index must reach into the data BTree (after reaching into the index BTree) to get the rest of the columns. That is, "covering" is more efficient.
Note: Even if the ids are
VARCHAR, the benefit of 'covering' outweighs the string-vs-int argument.
More on many-to-many: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
Another Case 2 example: wp_postmeta is inefficient . That applies to other EAV schemas, as well.
I find that 2/3 of the time, a 'natural' PK can be made from a column (or combination of columns).
InnoDB engine autocreate the internal PK if it isn't declared explicitly. That PK is autoincremented integer anyway so there is no performance difference between table with or without explicit
id field. Single-column index is faster then multicolumn ones and INT-based index is faster than other types.
The only difference is that you can refer to the explicit
id field and can't refer to the hidden field created by engine.