How We Made CiviCRM Full-Text Search 44x Faster, Now in CiviCRM 6.19

CiviCRM full-text search is faster in CiviCRM 6.19. We found why the Full-text Search screen stayed slow even with MySQL full-text indexes turned on, rewrote the query behind it, and the CiviCRM core team merged the fix. On a nonprofit CiviCRM database with about 76,000 contacts and 169,000 activities, that query ran 44 times faster across ten search terms and returned exactly the same results.

At a glance

• Release: CiviCRM 6.19.0, released October 7, 2026.
• Change: dev/core#6679, pull request 36451, merged September 2, 2026.
• What gets faster: the Full-text Search screen, when InnoDB full-text search is turned on.
• Result: total query time for ten search terms went from 43.8 seconds to 0.99 seconds (44x), with identical results.
• What to do: upgrade to CiviCRM 6.19 or later and check that InnoDB full-text search is on.

Why was CiviCRM full-text search slow?

The Full-text Search screen looks for your term in contacts, activities, cases, contributions, memberships and event participants. Behind the screen, CiviCRM runs a separate database query for each of them. One query did most of the work: it finds activities whose contact's name matches the term, plus email activities linked to a contact whose email address matches. On its own, that query took about 88% of the time the whole screen needed.

CiviCRM has a setting meant to speed this up, InnoDB full-text search. Turning it on adds full-text indexes to the tables CiviCRM searches. An index works like the index at the back of a book: the database goes straight to the matching rows instead of reading all of them.

The trouble was in how the query asked its question. It put both conditions into one WHERE clause joined by OR: the contact's name matches, or the email address matches. MySQL cannot use a full-text index for a MATCH that is only one side of an OR. So it used neither index and checked every activity that had not been deleted. The old query also joined the full list of activity types into every row, which multiplied the work.

MySQL's query plan (the output of EXPLAIN) shows it. The full-text index is listed among the possible keys, and MySQL picks a different index, with an estimated 61,000 activity rows to start from. In the slow query log, one search took 2.963 seconds and examined 2,735,023 rows.

That is also why turning on InnoDB full-text search barely helped. For the whole screen, a search for "registration" took 4,723 milliseconds with the setting off and 2,911 milliseconds with it on, still almost three seconds for one search.

What did we change?

We split the query in two. One part starts from contacts and finds activities by contact name. The other starts from email addresses and finds email activities. A UNION combines the two lists. Each part now has a single MATCH, so MySQL can use the right full-text index for each one. Here is the query, slightly shortened:

-- Before: one query, two MATCH conditions joined by OR
SELECT DISTINCT ca.id
FROM civicrm_activity ca
INNER JOIN civicrm_activity_contact cat ON cat.activity_id = ca.id
INNER JOIN civicrm_contact c ON cat.contact_id = c.id
LEFT JOIN civicrm_email e ON cat.contact_id = e.contact_id
LEFT JOIN civicrm_option_group og ON og.name = 'activity_type'
LEFT JOIN civicrm_option_value ov ON ov.option_group_id = og.id
WHERE (
  MATCH (c.sort_name, c.display_name, c.nick_name) AGAINST ('+"registration"' IN BOOLEAN MODE)
  OR
  (MATCH (e.email) AGAINST ('+"registration"' IN BOOLEAN MODE)
    AND ca.activity_type_id = ov.value AND ov.name IN ('Inbound Email', 'Email'))
)
AND ... -- deleted activities and contacts excluded

-- After: two queries, one MATCH each, combined with UNION
SELECT ca.id
FROM civicrm_contact c
INNER JOIN civicrm_activity_contact cat ON cat.contact_id = c.id
INNER JOIN civicrm_activity ca ON ca.id = cat.activity_id
WHERE MATCH (c.sort_name, c.display_name, c.nick_name) AGAINST ('+"registration"' IN BOOLEAN MODE)
AND ... -- deleted activities and contacts excluded
UNION
SELECT ca.id
FROM civicrm_email e
INNER JOIN civicrm_activity_contact cat ON cat.contact_id = e.contact_id
INNER JOIN civicrm_activity ca ON ca.id = cat.activity_id
INNER JOIN civicrm_contact c ON c.id = cat.contact_id
INNER JOIN civicrm_option_value ov ON ov.value = ca.activity_type_id AND ov.name IN ('Inbound Email', 'Email')
INNER JOIN civicrm_option_group og ON og.id = ov.option_group_id AND og.name = 'activity_type'
WHERE MATCH (e.email) AGAINST ('+"registration"' IN BOOLEAN MODE)
AND ... -- deleted activities and contacts excluded

The activity type lookup now lives only in the email part, the one place it is used, and it is joined on the activity's type instead of being joined into every row.

We compared full result sets, not only row counts, because a faster query is no use if it returns different activities. For ten search terms we checked the row count and a checksum of every activity ID from the old and the new query. All ten matched, including result sets of 11,168 and 16,621 IDs.

Only the activity query had this shape. The queries for contacts, cases, contributions, memberships and participants each have a single MATCH and already use their indexes, so they did not need a change.

How much faster is CiviCRM full-text search now?

We measured on CiviCRM 6.16.5 with MySQL 8.0, PHP 8.3 and Drupal 10.6, using a nonprofit CiviCRM database with about 76,000 contacts and 169,000 activities.

• One search, activity query only, from the slow query log: 2.963 seconds before, 0.009 seconds after.
• Ten search terms, total query time (each term run twice on the same server and data, faster run counted): 43.813 seconds before, 0.991 seconds after. That is the 44x.
• The slowest term in the set, a common email domain: 10.412 seconds before, 0.644 seconds after.
• The whole Full-text Search screen, end to end, searching for "registration": 4,723 ms with InnoDB full-text search off, 2,911 ms with it on, 45 ms with it on plus the fix.

The gain depends on the term. For the individual terms listed in the pull request it ranged from 16x to more than 4,000x, which is why we quote the total for all ten. We tested on MySQL 8.0 only, not on MariaDB.

What should CiviCRM site owners do?

1. Upgrade to CiviCRM 6.19 or later. The fix is part of CiviCRM itself, so there is nothing extra to install.
2. Check that InnoDB full-text search is on: Administer, Customize Data and Screens, Search Preferences (civicrm/admin/setting/search), setting "InnoDB Full Text Search". CiviCRM's help text says it requires MySQL 5.6 or later.
3. If it is off, pick a quiet time to turn it on. CiviCRM then builds full-text indexes on ten of its tables, which can take a while and use extra disk space on a large database. Take a backup first.
4. Run the searches your team uses most and compare the timing with what you had before.

The setting belongs to the Legacy Custom Searches extension, which ships with CiviCRM and provides the Full-text Search screen. Don't confuse it with "Use Mysql Full Text Search", a separate setting added in CiviCRM 6.17 for the newer APIv4-based search. The Full-text Search screen uses the older InnoDB setting.

If you cannot upgrade yet: the change is small, and we first measured it on CiviCRM 6.16.5, so a developer can apply the pull request as a patch until you move to 6.19.

A tip for anyone writing MATCH queries

CiviCRM 6.19 also adds APIv4 support for MySQL full-text search, and work has started on MATCH support in SearchKit, so more people will write queries like this one. If you write your own, keep each MATCH in its own query. Two MATCH conditions joined by OR across different tables can stop MySQL from using either index. Splitting them with UNION, as we did here, lets each one use its index.

Why we fixed it in CiviCRM, not on one site

We could have patched one site and moved on. A local patch, though, has to be carried through every upgrade, and it helps only one organization. Sending the fix to CiviCRM means every site that upgrades gets it, and our clients stay on standard CiviCRM with no custom patch to maintain.

The CiviCRM core team reviewed the benchmarks and merged the change on September 2, 2026. The 6.19 release notes list "iTech4Web - Dima" among the code authors. We work the same way in the Drupal community, where we publish modules and fixes on Drupal.org.

Need help with CiviCRM or Drupal?

We build and support Drupal websites for nonprofits and other organizations, including sites that run CiviCRM. If your CiviCRM searches feel slow, or you want help planning the upgrade to 6.19, ask us for a free technical audit. We will look at what slows your site down and tell you what to fix first.

Ask for a free technical audit

Looking for ongoing help? See how our website support works.

Sources: pull request 36451 (full benchmarks and EXPLAIN output), issue dev/core#6679, CiviCRM 6.19.0 release notes, CiviCRM 6.19 release announcement.