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