Quick search should use FTS by default
Modern search systems have trained users to interactively tweak their search-text. For example, if I'm searching for a person named "Joeseph Smith", I might go through a series of searches (depending on the particular data-set, number of matches, and quality of the search):
- "Joe"
- "Joe Smi"
- "Joe Smith"
- "Joseph"
- "Joseph Smith"
- "Smith"
- "Smith J"
- "Smith, J"
Unfortunately, the default quick-search in Civi doesn't handle this well. Anecdotally, I feel like half of my searches (regardless of how much I tweak the search-text) don't produce the expected result -- even if I hit enter and drill-down to view a longer list of results.
A big part of this originates with a mix of compatibility/complexity concerns. CiviCRM stores contacts with MySQL InnoDB, and (during much of Civi's development) InnoDB's best filter option was LIKE
... so that's what it uses. But LIKE
is not very effective for matching names. Of course, MySQL has evolved -- first, MyISAM gained support for "full text search" (FTS); then, in v5.6, InnoDB also gained support for FTS. Civi has some options to use InnoDB FTS, but they're not enabled by default, and (IIRC) they're not integrated with the quick-search. There are third-party search systems (like Solr and ElasticSearch), but they're significant additional deployment/installation matter. Consequently, there is no general resolution to this problem (although some sites may have customizations in this space).
Opinions
InnoDB doesn't have the best performance reputation - e.g. (1) (2) - but it is the simplest option from an architectural POV. It can search the existing data-stores, and it's supported on any deployment with MySQL v5.6+. To make this architecture compatible with all deployments, all we really need is a slight bump in the system-requirements. Future-You will thank Current-You to please keep the architecture simple.
But the performance is a real consideration. So I'd vote to take an empirical decision-making approach:
-
Do credible benchmarking of some realistic searches using InnoDB FTS. That basically means -- setup a dev site, load a bazillion contacts, add some FTS indices, spy on the "Advanced Search" to grab some example SQL queries, and then manually tweak+execute to see if FTS-enabled variants perform alright. This can answer some key questions without requiring a lot of design discussion.
-
If InnoDB FTS performance is acceptable, then this is great. There's no need for a major new data-layer. We should simply re-engineer the default "Quick Search" to use InnoDB FTS (with some LExIM provisos), and we should bump the MySQL requirements (strictly v5.6+ or v5.7+).
-
If InnoDB FTS does not perform well, then solving the search-usability issue will require something else -- e.g. mapping into some other system (e.g. MyISAM FTS, Solr, Elastisearch). This is a more complicated path. I don't want to rule it out, but I'd vote to only look at it if the simpler/KISS architecture doesn't cut it.