When a segment filter is performed, mautic run a query like this:
XXX
the field depends on what you search in
and the % depends on whether you chosed contain, begin or end with filter.
Still LIKE is very slow.
INNODB tables support Full text search from mysql 5.6. So it should be very much possible to perform these lookups with full text search instead of a full table search
SEL ECT count(leadIdPrimary) count, max(leadIdPrimary)
maxId, min(leadIdPrimary) minId FROM (SEL ECT DIS TINCT l.id as leadIdPrimary, OAeZCtpV.lead_id AS OAeZCtpV_lead_id F
ROM leads l LEFT JOIN lead_lists_leads OAeZCtpV ON OAeZCtpV.lead_id = l.id and (OAeZCtpV.leadlist_id = 5) WHERE (EXIS
TS(SEL ECT NULL FROM page_hits dEPB
sfGI WHERE (dEPBsfGI.lead_id = l.id) AND (dEP
BsfGI.url LIKE ‘%SEARC HWORD%’))) AND (OAeZCtpV.lead_id IS NULL)) sss