Magento 2 slow reindex using MariDB

[nkajic]

Version of Warden

0.15.0

Operating System and Installation Method

MacOS 15.3

Image

Magento 2 templates

Describe the Bug

Hi all,

Magento 2 configuration for MariaDB does not follow Adobe recommendations.
Specially visible on large projects.

Please see
https://experienceleague.adobe.com/en/docs/commerce-cloud-service/user-guide/configure/service/mysql
under "Configure MySQL database".

Please add following configuration for MariaDB
optimizer_switch: "rowid_filter=off"
optimizer_use_condition_selectivity: 1

Not setting those variables result in x10 or x20 order of magnitude in reindexing time for Category products alone.
Also seen unproperly configured on many production webshops

More context:
Same problem occurred on different project
Mark Shust's Docker Configuration for Magento
Fix possible slow reindexing on MariaDB no. 705
markshust/docker-magento#705

To Reproduce

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

Expected Behavior

No response

Additional context

No response

[bap14]

I think this is personally just an adjustment to the default environment yaml file to add these to the mysql command. Enabling this at the image level for everyone may not be appropriate, and so I'd err on the side of caution to only enable this for environments that it makes sense for, as well as more easily allowing for custom environments to add to the same.

If we do it this way, we could create new global ~/.warden/env variables that control these items, and then update the environment .env template files for the same. This would mean more manual work during upgrades (or a way to "diff" the template env with the existing env and add any missing variables with the default value).