Database Reference
In-Depth Information
▼
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).
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');
