Improve performance of queries for search filters

[mallezie]

Mautic Series

5.2.x series

Mautic installed version

5.2.3

Way of installing

I installed with composer using https://github.com/mautic/recommended-project

PHP version

8.1

What browsers are you seeing the problem on?

Not relevant

What happened?

When using (non base table) searches in the contact overview, the generated query is not optimal, causing performance impact an possible time outs.

How can we reproduce this issue?

Step 1: Have a mautic with preferably a large amount of contacts.
Step 2: Use a search filter, targeting a seperate table, for example filter on a segment with that filter.

The problem lies that this query transforms the filter to a where clause.
See https://github.com/mautic/mautic/blob/6.x/app/bundles/LeadBundle/Entity/CustomFieldRepositoryTrait.php#L45

The where clause generated is a subquery of the other table it needs to reference. See https://github.com/mautic/mautic/blob/6.x/app/bundles/CoreBundle/Entity/CommonRepository.php#L1454

For example getting contacts from a segment uses below query.

SELECT
	l.*
FROM
	leads l USE INDEX FOR
JOIN (lead_date_added)
WHERE
	(EXISTS(
	SELECT
		1
	FROM
		lead_lists_leads lla
	WHERE
		(l.id = lla.lead_id)
			AND (lla.manually_removed = 0)
				AND (lla.leadlist_id IN (994))))
	AND (l.date_identified IS NOT NULL)
ORDER BY
	l.last_active DESC,
	l.id DESC
LIMIT 200

On our instances (1.5M leads) this takes 6+ seconds.
If the building of this query could use a join instead of a subquery in the where statement, time goes down to less than .1 seconds.

Main problem where this occurs is when exporting the list of contacts to xls or csv, there this query runs per batch of 200 contacts (so 6+ seconds per batch) and times out. For example, list of 1000 contacts, runs that 5 times, which is 30+ seconds, so you encounter quite quickly a php timeout.

Relevant log output

Code of Conduct

• I confirm that I have read and agree to follow this project's Code of Conduct

---

Care about this issue? Want to get it resolved sooner? If you are a member of Mautic, you can add some funds to the Bounties Project so that the person who completes this task can claim those funds once it is merged by a member of the core team! Read the docs here.

[RCheesley]

Thanks for the bug report @mallezie - will your team be up for making a PR to optimise these queries?

[mallezie]

@RCheesley not in the near future we are planning to work on this. It might probably also be a huge undertaking to fix this, so not really sure where to start as well. IMO a fix would be a full rework of the search part on contacts to step away from the generic add text based filters, as long as this is needed i see why it uses this kind of query, since it works for everything, just scalability is the problem.

As a current workaround we or the users can create reports and export those, which is okay for u for now for the export part, where this issue hits the hardest.

Moslty wanted to file it here (or anywhere) if others encounter this, so google might point to here at least.