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
Always test in the Staging environment prior to making any changes to the Production environment.
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.