Best practice for triggers usage

This article explains how to avoid performance issues when using MySQL triggers. Triggers are used to log changes into audit tables.

Affected products and versions

• Adobe Commerce on-premises, all supported versions
• Adobe Commerce on cloud infrastructure, all supported versions

Best practices

Triggers are interpreted as code meaning that MySQL does not precompile them.

Hooking into the query’s transaction space, triggers add overhead to a parser and interpreter for each query performed with the table. The triggers share the same transaction space as the original queries, and while those queries compete for locks on the table, the triggers independently compete for locks on another table.

This additional overhead can have a negative performance on the site if many triggers are used.

To avoid an issue with triggers negatively impacting performance follow these best practices:

• If you have custom triggers that write some data when the trigger is executed, move this logic to write directly to the audit tables instead. For example, by adding an additional query in the application code, after the query you aimed to create the trigger for.
• Review existing custom triggers and consider removing them and writing directly to the tables from the application side. You can check for existing triggers in your database by following steps in MySQL 8.0 Reference Manual > SHOW TRIGGERS Statement.
• If assistance is required or if there are questions or concerns, submit a Adobe Commerce Support ticket.

Related reading

• To learn more about MySQL triggers, refer to Installation Guide > MySQL in our developer documentation.
• To learn about database best practices, refer to Database best practices for Adobe Commerce on cloud infrastructure in our support knowledge base.