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?

  • Does your colleague use a natural key? – Vérace May 17 '18 at 5:17
  • @Vérace Not sure but i dont think so. – Unknown May 17 '18 at 6:06
  • To create links between data stored in tables You must have unique key in table on the 'one' side of link. In MySQL the difference between primary and unique key is small except the absence of PRI leads to small (6 byte per record for InnoDB, for example) overhead. While You use natural, not synthetic, pri/uni key for linking, which are of string type in most cases, You almost certainly decrease the performance of Your queries. – Akina May 17 '18 at 6:10
  • @Akina Our tables contains milions of data. Specific tables contains either raw data and also upper level tables which are based on inner joins based on raw datas, however my colelague are not making AI PK along all tables he said that its not good regarding performance. – Unknown May 17 '18 at 6:15
  • 1
    Another one reason. One field must commonly carry out only one function. PK function is to identify the record unique during the whole period of table (not record!) life. When PK is natural one, it performs two different functions - store data and identify record. It's bad in most cases, moreover, while change the structure in future this functions can became conflicted. – Akina May 17 '18 at 7:00

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.

  • so that means i cannot use Innodb's autogenerated one. Therefore creating col Id AI PK is good habbit? How could i explain it to my colleague who saying it's bad for performance. – Unknown May 17 '18 at 7:49
  • You can refer to the chapter of the mysql manual. Separate id field contains not only some tag to tell one record from another but also the order rows been inserted into the table. That additional information can become crucial in some cases. As far as that column/index exists anyway it is better to have the control over them. – Kondybas May 17 '18 at 8:20
  • Also we can't rely on incoming data to be unique/not-null to use column(s) for PK. Additional autoincremented field is more (not absolutely) reliable . – Kondybas May 17 '18 at 8:25
  • has really nice information but i still not see anything about performance my colleague is refering to. – Unknown May 17 '18 at 9:49

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 INT AI.

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).

  • Case 2: There is a natural PK and you need an index by that column(s). Bingo! You forget one word. NOW. Imagine for a moment that in the future this provision will change for some reason which is now unpredictable... and uniqueness of this natural PK became a dust. – Akina May 28 '18 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.