0

Years ago with MySQL/InnoDB, I've researched and found that adding a surrogate integer primary key to a table which doesn't have it always makes sense for performance reason. Even if that key is never used in JOINs or child tables. That's because of the clustered index used in InnoDB meaning the table records are sorted by the primary key and as such, any key that's not a simple AUTOINC may mean different pages have to be continuously accessed and updated when making INSERTs rather than just the last page.

As we're now with MariaDB/InnoDB and I can't seem to find updated data on this topic, I'm asking this question in hopes for additional specific references and performance/integrity considerations.

It is quite clear that primary keys should always exist and integer (the smaller the better) is the preferred thing to have but in case such doesn't naturally make sense for the table, should some fakeid primary key be created, even when there are no JOINs done which will use it directly nor are there any child tables?

I'm guessing the answer is a yes if the natural primary key is a string but what if it's a composite primary key? For example my_table(category_id, item_id, detailA, detailB) where the natural primary key would be category_id+item_id (both integers)? Would it make sense to add an integer fakeid to that table so that we'll have a single integer primary key?

This question isn't about the theoretical SQL concept but about current implementation of MariaDB (now at v10.6.x).

1 Answer 1

1

I will use id to refer to a surrogate auto_increment column (usually, but not necessarily, the PRIMARY KEY). Most people use INT; many platforms blindly use the excessively large BIGINT.

I will refer to a "natural" PK as some non-auto_inc column or combination of columns that uniquely identifies each row.

  • If you have a "natural" key, use it. Example: countries can have a CHAR(2) PK with the standard country_code. This is doubly good because CHAR(2) is smaller than INT (though bigger than TINYINT).
  • A many-to-many mapping table actually works better with PRIMARY KEY(aid, bid), INDEX(bid, aid). The id is unnecessary clutter.
  • A UUID is, has a few advantages, but lots of disadvantages.
  • As for bulk, consider the disk space tradeoff between just id versus a bulkier, perhaps multi-column, PK. Note that InnoDB appends the PK to each secondary key. A table with just the PK will be smaller with a natural key. For a PK plus one secondary key, the size will be about the same. For a PK plus 2+ secondaries, the table will be bulkier with a bulky PK.
  • I have made hundreds of tables; 2/3 of them used a 'natural' PK, not id. (In making a table, I always consciously think about what PK and other indexes to have.)
  • For "clustering" and "locality of reference", here's a combo trick:

Given, say, Users and Posts (1 user to many posts mapping):

Plan A, The obvious way to design Posts:

CREATE TABLE Posts (
    post_id ... AUTO_INCREMENT,
    user_id ...,
    (etc)
    PRIMARY KEY(post_id),
    INDEX(user_id),
    (optionally more indexes)

Plan B:

CREATE TABLE Posts (
    post_id ... AUTO_INCREMENT,
    user_id ...,
    (etc)
    PRIMARY KEY(user_id, post_id),  -- to provide clustering
    INDEX(post_id),   -- to keep auto_increment happy
    (optionally more indexes)

Plan B significantly improves performance for finding all the posts for one user. The cost is that any other indexes may be a little bigger.

For daily stock quotes, I recommend this natural PK:

CREATE TABLE Quotes (
    stock_id ...
    date ...
    ...
    PRIMARY KEY(stock_id, date),   -- in this order
    INDEX(date)  -- only if needed

The nightly upload of quotes is scattered, but the fetch to graph one stock's data is neatly clustered.

3
  • Thank you for the detailed answer. What made you choose the practices you mention? For example you write "A many-to-many mapping table actually works better with PRIMARY KEY(aid, bid), INDEX(bid, aid). The id is unnecessary clutter." but this may contradict "As for bulk, consider the disk space tradeoff between just id versus a bulkier, perhaps multi-column, PK."
    – Collector
    Jan 7, 2023 at 6:43
  • @Collector - Note my "as for bulk" leaves 2-index tables being a tossup. Keep in mind that saying INDEX(foo) generates a BTree with both foo and any other columns that are in the PK. continued...
    – Rick James
    Jan 7, 2023 at 16:28
  • 1
    If the table has PK(id), index(aid), index(bid), then you have 3 BTrees: (id, aid, bid), (aid,id), (bid, id). It is twice as much work to go from aid through id to get to bid. In contrast, if the table is PK(aid, bid), index(bid, aid), that's two BTrees with 2 columns each. And no extra hop. So the disk space and speed are improved.
    – Rick James
    Jan 7, 2023 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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