MySQL has supported Full-Text Search (FTS) functionality for quite some time. The FTS capability was already introduced in MySQL version 3.23.23, which was released in September 2001, and yet I have never used it - until now.
A Full-Text Search is an engine designed to search and retrieve information from a database based on the content of its documents. Unlike traditional databases that rely on exact matches of keywords or phrases, a FTS allows for more flexible and natural language searching.
In other words: You do not need to add complex SQL queries with a combination of AND, OR and LIKE.
In MySQL, a Full-Text Search is performed by using the function MATCH(x) AGAINST(y) where x is a comma separated list of the columns
and y is the actual search term. The comma separated list of columns must also have an index with the type of FULLTEXT.
MySQL has different modes for the search: The Natural Language Mode and the Boolean Mode.
In the Natural Language Mode, which is the default mode in MySQL, you can just type in a free text and get the result. There are no special operators and the result will be ordered automatically by the relevance of the rows.
mysql> SELECT
-> name,
-> city,
-> MATCH(name, city) AGAINST ('Eagle Small Los') as score
-> FROM
-> location
-> WHERE
-> MATCH(name, city) AGAINST ('Eagle Small Los');
+-------------+-------------+--------------------+
| name | city | score |
+-------------+-------------+--------------------+
| Small Eagle | Los Angeles | 14.405424118041992 |
| Eagle | Los Banos | 8.705794334411621 |
+-------------+-------------+--------------------+
2 rows in set (0.00 sec)
As you notice, it also matches rows where the word small is not present in but the one with it gets a higher score which
will automatically be ordered before.
The Boolean Mode is much more flexible. You can use different operators on the search string before each word:
+ stands for AND so the word must be included- stands for NOT so the word must NOT be included> stands for increasing of relevance< stands for decreasing of relevance( ) group* stands for a wildcard (can only be appended)In the following example, all words must be present:
mysql> SELECT
-> name,
-> city
-> FROM
-> location
-> WHERE
-> MATCH(name, city) AGAINST ('+Eagle +Small +Los' in BOOLEAN MODE);
+-------------+-------------+
| name | city |
+-------------+-------------+
| Small Eagle | Los Angeles |
+-------------+-------------+
1 row in set (0.00 sec)
In the following tutorial, I will create a Full-Text search for the entity Location based on the name and city of a
location.
Since the MySQL functions used for a Full-Text Search are not integrated into the Doctrine Core, you need to install the package beberlei/doctrineextensions first which adds support for a lot of database functions for MySQL, PostgreSQL, Oracle and SQLite.
composer require beberlei/doctrineextensions
Now register the required function MATCH in the doctrine.yml configuration.
doctrine:
orm:
dql:
string_functions:
MATCH: DoctrineExtensions\Query\Mysql\MatchAgainst
Add the following attribute to the entity Location to create the composite full-text index with the name
FULLTEXT__CITY_NAME consisting of the two columns name and city.
...
#[Index(fields: ["name","city"], name: "FULLTEXT__CITY_NAME", flags: ["fulltext"])]
Class Location
...
Afterward, create the migration and run it. The migration should look like this:
public function up(Schema $schema): void
{
// this up() migration is auto-generated, please modify it to your needs
$this->addSql('CREATE FULLTEXT INDEX FULLTEXT__CITY_NAME ON location (name, city)');
}
Now I want to search for all locations which contain both strings Eagle and Los.
The SQL query looks like this...
SELECT
name,
city
FROM
location
WHERE
MATCH(name, city) AGAINST ('+Eagle* +Los*' IN BOOLEAN MODE);
... and will return following result:
+-------------+-------------+
| name | city |
+-------------+-------------+
| Eagle | Los Banos |
| Small Eagle | Los Angeles |
+-------------+-------------+
2 rows in set (0.00 sec)
Now the PHP code for the Doctrine Query Builder looks like this:
$qb->andWhere('MATCH (l.name, l.city) AGAINST (:search IN BOOLEAN MODE)');
$qb->setParameter('search', '+Eagle* +Los*'));
Since I use the + sign before each word, they must be present. The BOOLEAN MODE does not sort the results in order of
their relevance. If you want order the result, you must add the MATCH as SELECT and an order by score DESC.
$qb->addSelect('MATCH (l.name, l.city) AGAINST (:search IN BOOLEAN MODE) as SCORE');
$qb->orderBy('SCORE', 'DESC');
If you want to add a higher search weight based on a column, you will have to add another index just for that column.
Since the Full-Text Search functionality is very basic, only a few options for fine-tuning are available like adding stop words or configuring the minimum and maximum word length.
The tokenizer can't be adjusted and maybe does not work as expected, since it breaks words with a hyphen since it is not treated as word character.
Also, you cannot search across different tables by one index since you cannot add an index across different tables and a view cannot have an index.
See the full list for fine-tuning.
I have built Full-Text Searches with SOLR, OpenSearch and Meilisearch - and each one of them is much more powerful than the MySQL. I mean hopefully they are - because all of them are separate products specialized on full-text search.
But if you are currently using complex and slow queries with MySQL as a workaround, you really should check out the MySQL
Full-Text search since it can be a really low-hanging fruit to improve the performance of your search and also the developer
experience without adding a new service as a dependency.