• Choose a category
• All
• Classic-Fiction

All these limitations and more are addressed by full-text searching. When full-text searching is used, MariaDB does not need to look at each row individually, analyzing and processing each word individually. Rather, an index of the words (in specified columns) is created by MariaDB, and searches can be made against those words. MariaDB can thus quickly and efficiently determine which words match (which rows contain them), which don’t, how often they match, and so on.

Using Full-Text Searching

To perform full-text searches, the columns to be searched must be indexed and constantly reindexed as data changes. MariaDB handles all indexing and reindexing automatically after table columns have been appropriately designated.

After indexing, SELECT can be used with Match() and Against() to actually perform the searches.

Enabling Full-Text Searching Support

Generally, full-text searching is enabled when a table is created. The CREATE TABLE statement (which is introduced in Chapter 21) accepts a FULLTEXT clause, which is a comma-delimited list of the columns to be indexed.

The following CREATE statement demonstrates the use of the FULLTEXT clause:

Input

CREATE TABLE productnotes

(

note_id int NOT NULL AUTO_INCREMENT,

prod_id char(10) NOT NULL,

note_date datetime NOT NULL,

note_text text NULL ,

PRIMARY KEY(note_id),

FULLTEXT(note_text)

) ENGINE=Maria;

Analysis

We look at the CREATE TABLE statement in detail in Chapter 21. For now, just note that this CREATE_TABLE statement defines table productnotes and lists the columns that it is to contain. One of those columns is named note_text, and it is indexed by MariaDB for full-text searching as instructed by the clause FULLTEXT(note_text). Here FULLTEXT indexes a single column, but multiple columns may be specified if needed.

Once defined, MariaDB automatically maintains the index. When rows are added, updated, or deleted, the index is automatically updated accordingly.

FULLTEXT may be specified at table creation time, or later on (in which case all existing data would have to be immediately indexed).

* * *

Tip: Don’t Use FULLTEXT When Importing Data

Updating indexes takes time—not a lot of time, but time nonetheless. If you are importing data into a new table, you should not enable FULLTEXT indexing at that time. Rather, first import all the data, and then modify the table to define FULLTEXT. This makes for a much faster data import (and the total time needed to index all data will be less than the sum of the time needed to index each row individually).

* * *

Performing Full-Text Searches

After indexing, full-text searches are performed using two functions: Match() to specify the columns to be searched and Against() to specify the search expression to be used.

Here is a basic example:

Input

SELECT note_text

FROM productnotes

WHERE Match(note_text) Against('rabbit');

Output

+---------------------------------------------------------------------------+

| note_text |

+---------------------------------------------------------------------------|

| Customer complaint: rabbit has been able to detect trap, food apparently |

| less effective now. |

| Quantity varies, sold by the sack load. All guaranteed to be bright and |

| orange, and suitable for use as rabbit bait. |

+---------------------------------------------------------------------------+

Analysis

The SELECT statement retrieves a single column, note_text. For the WHERE clause, a full-text search is performed. Match(note_text) instructs MariaDB to perform the search against that named column, and Against('rabbit') specifies the word rabbit as the search text. As two rows contained the word rabbit, those two rows were returned.

* * *

Note: Use Full Match() Specification

The value passed to Match() must be the same as the one used in the FULLTEXT() definition. If multiple columns are specified, all of them must be listed (and in the correct order).

* * *

* * *

Note: