What are the use cases for MySQL Generated Invisible Primary Keys (GIPKs)?

Question

I have read in the MySQL documentation that it is possible to enable the generation of invisible primary keys (https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html).

I'm quite a newbie in DB administration so I would like to understand how adding an invisible primary key could improve the performance of SELECT queries if the invisible column that is generated is not used in the WHERE clauses of such queries. And if it can't improve performance, then what would be the interest of enabling this feature ?

Answer 1

Not sure where you found that GIPKs can improve performance. They are useful when setting up logical replication though; to replicate an update or delete operation, you must be able to uniquely identify the row(s) to be updated or deleted on the receiving end. If the table has no primary key, this is impossible.

Subsequently, this feature can help when setting up a database cluster for an application that has no properly designed database schema, particularly if it is a third-party app that you cannot modify. The extra column will remain invisible to the application and won't affect its logic, while allowing you to create replicas of its database for improved availability or to offload read-only workloads.