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?
Great question. We have a feature with our Mautic fork that the user is able to configure in the UI whether the column is indexable or not. They also can configure the text field (VARCHAR) size which also helps optimizing indexes. Not all fields need full 191 characters.
So to answer your question, go ahead and manage your indexes and VARCHAR column lengths on the database level.
We were discussing with the team that letting the users configure if the field needs an index or the char length limit is not really user friendly and that there should be a command that would figure it out itself and modify these changes on a daily basis. But that’s a future feature.
I’d like to promise we’ll push this PR soon, but as many of our open PRs just sit there for months and years without any community involvement and getting stale then it kinda doesn’t make sense to push more until those that are open are merged.
We will move forward adding columns as needed and not freak out about doctrine wanting indexes on stuff. I would love to see that PR make its way to the community and would be happy to test it.
When creating a new text field, users can define the length. If you know you need 100 character max, it’s better to set it for the field and then you can create more such smaller fields.
There is a new command that will tell you if you can reduce some size of a custom field (column on the leads table) based on the data that are present in the column. This way you can easily get the data for optimizing the custom fields and making space for more columns.
I’d suggest to use AI to do that these days. You can describe what you want, ideally provide the database table schema in your prompt and tell it to write a alter query to shorten/delete/create the index you need. It will give you the query you can run.