MySQL Primary Keys are Not Defined

[lbve]

Defining Primary Keys (PK) is a requirement for a good database and table design. Primary Keys (PK) provides a method to uniquely identify a single row in any table. Using the InnoDB engine, which is the Magento default, in tables where no PK is defined, the first unique non-null key is the Primary Key.

If no PK is available, InnoDB creates a hidden primary key (6 bytes). The problem with such type of key is that there is no control over it and its value is global for all tables without Primary Keys. This can cause contention problems performing simultaneous writes on these tables. This can lead to performance issues as they all share that global hidden PK index increment.

Preconditions

Magento Version: Magento EE (Should be applied also on CE)

ElasticSuite Version: 2.11.8

Environment: Production

Third party modules: None

Steps to reproduce

1. Run installation of smile/elasticsuite
2. Check the primary keys of new sql tables

Expected result

1. All tables should have a primary key

Actual result

1. smile_elasticsuite_optimizer_limitation does not have primary key

This issue can cause contention problems performing simultaneous writes on these tables. This can lead to performance issues from deadlocks

[rbayet]

Hello @lbve,

Indeed, column optimizer_id acts as an implicit PK considering it's a non-nullable column with a FK on the PK of smile_elasticsuite_optimizer. Plus the code updates or removes and adds limitation entries when an optimizer is saved (as best as I can remember).

Have you noticed yourself issues in a context with other native or third-party provided DB tables not having a PK ?

Regards,

[lbve]

Hello @rbayet , we got a SWAT report from Magento where multiple 3rd modules has tables without PK. So we were advised to proceed with the changes to the tables, we believe it is appropriate that you proceed in this direction.