I’m hoping to get a definitive answer on if it’s ok to add more custom fields to entities (i.e. contacts and companies) than MySQL can create indexes for, provided you manage which indexes exist.
From what I can tell, Mautic 4 does not impose a limit on the number of custom fields you can create from the UI. However, Symfony/Doctrine defaults to creating an index for each new field created. A some point, the fields you add do not get indexed because MySQL and MariaDB both have hard limits of 64 keys per table, and this actually results in a silent error in the UI and the screen does not refresh.
That said, the field is created and can be used. The trade-off, I’m assuming, is poor performance if it’s used in segments and probably other parts of Mautic because the index doesn’t exist.
Past experience (dating back to Mautic 2) leaves me with the understanding that creating these extra fields comes back to bite you at some point, usually at upgrade time when a migration will fail because the database is out of sync with the ORM. However, I don’t have any evidence that is still the case.
I know that you can always inspect the ways Doctrine is out of sync with MySQL using the command line (bin/console doctrine:schema:update --dump-sql
) and pick and choose your 64 keys wisely to make sure the custom fields that would be queried are indexed and the ones that will never be queried are not.
My question is what happens if I:
- Add as many custom fields as I need to accomplish our business needs
- Manually drop indexes from the fields that won’t be used in segments and create indexes for the fields that will, up to the 64 key limit
Is anyone else doing this?
Does anyone know if I’m creating a ticking time bomb and some upgrade, plugin install or some other function will fail in the future by doing this?