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. | +----+---------------------------------+---------------------------------------+