MySQL Full Text Search: Enable full text search support

Generally enable full text search when creating a table. The CREATE TABLE statement accepts the FULLTEXT clause, which gives a comma-separated list of indexed columns.

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 = MyISAM;

Analysis: There is a column named note_text among these columns. To perform a full-text search, MySQL indexes it as instructed by the clause FULLTEXT(note_text). The FULLTEXT here indexes a single column, and you can specify multiple columns if necessary.

After definition, MySQL automatically maintains the index. As rows are added, updated, or deleted, the index is automatically updated.

FULLTEXT can be specified when creating the table, or later (in which case all existing data must be indexed immediately).

Don’t use FULLTEXT when importing data. Updating the index takes time, although not a lot, but it takes time after all. If you are importing data into a new table, the FULLTEXT index should not be enabled at this time. All data should be imported first, and then the table modified to define FULLTEXT. This helps import the data faster (and makes the total time to index the data less than indexing each row individually as it is imported).

The above is the detailed content of MySQL Full Text Search: Enable full text search support. For more information, please follow other related articles on the PHP Chinese website!