How MATCH AGAINST Works in MariaDB

In MariaDB, MATCH AGAINST is a special construct used to perform a full-text search on a full-text index.

Syntax

The syntax goes like this:

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

Example

Suppose we have a table called Products that includes the following data:

+----+---------------------------------+-----------------------------------------+
| Id | ProductName                     | ProductDescription                      |
+----+---------------------------------+-----------------------------------------+
|  1 | Left handed screwdriver         | Purple. Includes left handed carry box. |
|  2 | Right handed screwdriver        | Blue. Includes right handed carry box.  |
|  3 | Long Weight (blue)              | Approximate 45 minute waiting period.   |
|  4 | Long Weight (green)             | Approximate 30 minute waiting period.   |
|  5 | Sledge Hammer                   | Wooden handle. Free wine glasses.       |
|  6 | Chainsaw                        | Orange. Includes spare fingers.         |
|  7 | Straw Dog Box                   | Tied with vines. Very chewable.         |
|  8 | Bottomless Coffee Mugs (4 Pack) | Brown ceramic with solid handle.        |
+----+---------------------------------+-----------------------------------------+

This table has a full-text index on its ProductDescription column. That means we can use MATCH AGAINST to do a full-text search against that column.

Example:

SELECT 
    ProductId AS "Id",
    ProductName,
    ProductDescription
FROM Products 
WHERE MATCH(ProductDescription) AGAINST('includes')
ORDER BY ProductId ASC;

Result:

+----+--------------------------+-----------------------------------------+
| Id | ProductName              | ProductDescription                      |
+----+--------------------------+-----------------------------------------+
|  1 | Left handed screwdriver  | Purple. Includes left handed carry box. |
|  2 | Right handed screwdriver | Blue. Includes right handed carry box.  |
|  6 | Chainsaw                 | Orange. Includes spare fingers.         |
+----+--------------------------+-----------------------------------------+

Get the Score

We can include MATCH AGAINST in the SELECT list in order to return the relevancy score of the keyword within the column/s searched:

SELECT 
    ProductDescription,
    MATCH(ProductDescription) AGAINST ('includes') AS Score
FROM Products 
WHERE MATCH(ProductDescription) AGAINST ('includes')
ORDER BY Score DESC;

Result:

+-----------------------------------------+---------------------+
| ProductDescription                      | Score               |
+-----------------------------------------+---------------------+
| Orange. Includes spare fingers.         |  0.4883610010147095 |
| Blue. Includes right handed carry box.  |  0.4883610010147095 |
| Purple. Includes left handed carry box. | 0.48305025696754456 |
+-----------------------------------------+---------------------+

In this case we also used an ORDER BY clause to sort by the score in descending order (i.e. most relevant first).

Columns Without a Full-Text Index

The reason the previous example worked is because I had previously created a full-text index on the ProductDescription column. If I hadn’t done this, I would have received an error.

Here’s what happens when we try to use MATCH AGAINST against a column that doesn’t have a full-text index:

SELECT 
    ProductId,
    ProductName,
    ProductPrice
FROM Products 
WHERE MATCH(ProductName) AGAINST('screwdriver')
ORDER BY ProductId ASC;

Result:

ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list

Let’s add a full-text index on the ProductName column:

ALTER TABLE Products
ADD FULLTEXT(ProductName);

Now run the query again:

SELECT 
    ProductId,
    ProductName,
    ProductPrice
FROM Products 
WHERE MATCH(ProductName) AGAINST('screwdriver')
ORDER BY ProductId ASC;

Result:

+-----------+--------------------------+--------------+
| ProductId | ProductName              | ProductPrice |
+-----------+--------------------------+--------------+
|         1 | Left handed screwdriver  |        25.99 |
|         2 | Right handed screwdriver |        25.99 |
+-----------+--------------------------+--------------+

This time it worked.

Full-Text Index on Multiple Columns

We can add full-text indexes on multiple columns.

Example:

ALTER TABLE Products
ADD FULLTEXT(ProductName, ProductDescription);

Now we can run MATCH AGAINST against that full-text index.

SELECT 
    ProductId AS Id, 
    ProductName, 
    ProductDescription
FROM Products 
WHERE MATCH(ProductName, ProductDescription) AGAINST ('weight')
OR MATCH(ProductName, ProductDescription) AGAINST ('ceramic')
ORDER BY Id ASC;

Result:

+----+---------------------------------+---------------------------------------+
| Id | ProductName                     | ProductDescription                    |
+----+---------------------------------+---------------------------------------+
|  3 | Long Weight (blue)              | Approximate 45 minute waiting period. |
|  4 | Long Weight (green)             | Approximate 30 minute waiting period. |
|  8 | Bottomless Coffee Mugs (4 Pack) | Brown ceramic with solid handle.      |
+----+---------------------------------+---------------------------------------+