Verify that all tables have a primary key
Defining primary keys is a requirement for good database and table design. Primary keys provide a way to uniquely identify a single row in any table.
If you have tables without a primary key, the default database engine for Adobe Commerce (InnoDB) uses the first unique not null key as the primary key. If no unique key is available, InnoDB creates a hidden primary key (6 bytes). The problem with an implicitly defined primary key is that you do not have control over it. Additionally, the implicit value is globally-assigned for all tables without primary keys. This configuration can cause contention problems if you perform simultaneous writes on these tables. This might lead to performance issues because the tables also share the global hidden primary key index increment.
Prevent these issues by defining a primary key for any tables that don’t have one.
Identify and update tables without a primary key
-
Identify tables without a primary key using the following SQL query:
SELECT table_catalog, table_schema, table_name, engine FROM information_schema.tables WHERE (table_catalog, table_schema, table_name) NOT IN (SELECT table_catalog, table_schema, table_name FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY') AND table_schema NOT IN ('information_schema', 'pg_catalog');
-
For any table missing a primary key, add a primary key by updating the
db_schema.xml
(the declarative schema) with a node similar to the following:<constraint xsi:type="primary" referenceId="PRIMARY"> <column name="id_column"/> </constraint>
When you add the node, replace the
referenceID
andcolumn name
variables with your custom custom values.
For more information, see Configure declarative schema in our developer documentation.
Identify and remove duplicate indexes
Identify any duplicate indexes in your database and remove them.
Check for duplicate indexes
To check for duplicate indexes on either Pro or Starter cloud architecture, run the following SQL query.
SELECT s.INDEXED_COL,GROUP_CONCAT(INDEX_NAME) FROM (SELECT INDEX_NAME,GROUP_CONCAT(CONCAT(TABLE_NAME,'.',COLUMN_NAME) ORDER BY CONCAT(SEQ_IN_INDEX,COLUMN_NAME)) 'INDEXED_COL' FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'db?' GROUP BY INDEX_NAME)as s GROUP BY INDEXED_COL HAVING COUNT(1)>1
The query returns the column names and the names of any duplicate indexes.
Pro architecture merchants can also run the check using the Percona Toolkit [pt-duplicate-key checker](https://www.percona.com/doc/percona-toolkit/LATEST/pt-duplicate-key-checker.html%C2%A0)
command.
Remove duplicate indexes
Use the SQL DROP INDEX Statement to remove duplicate indexes.
DROP INDEX