Database Reference
In-Depth Information
■
Explicit control
—Using wildcard and regular expression matching,
it is difficult (and not always possible) to explicitly control what is and
what is not matched. An example of this is a search specifying a word
that must be matched, a word that must not be matched, and a word
that may or may not be matched but only if the first word is indeed
matched.
■
Intelligent results
—Although wildcard- and regular expression-based
searching provide for flexible searching, neither provides an intelligent
way to select results. For example, searching for a specific word returns
all rows that contain that word and does not distinguish between rows
that contain a single match and those that contain multiple matches
(ranking them as potentially better matches). Similarly, searches for a
specific word does not find rows that do not contain that word but do
contain other related words.
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.
To perform full-text searches, the columns to be searched must be indexed and
constantly reindexed as data changes. MariaDB handles all indexing and rein-
dexing automatically after table columns have been appropriately designated.
After indexing,
SELECT
can be used with
Match()
and
Against()
to actu-
ally perform the searches.
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:
