Optimizing for Query Speed - 13.1.4 FULLTEXT Indexes (Page 7 of 7 )
FULLTEXT indexes are designed to make text searching fast and easy. They have the following characteristics:
FULLTEXT indexes currently are supported only for MyISAM tables.
Each column must be either CHAR or VARCHAR without the BINARY option, or one of the TEXT types. You cannot use a binary string column type such as CHAR BINARY, VARCHAR BINARY, or BLOB.
FULLTEXT indexes are not case sensitive. This is a consequence of the fact that the index can include only nonbinary string columns.
The syntax for defining a full-text index is much like that for other indexes: An index-type keyword (FULLTEXT), an optional index name, and a parenthesized list of one or more column names to be indexed. A FULLTEXT index may be created with CREATE TABLE, added to a table with ALTER TABLE or CREATE INDEX, and dropped from a table with ALTER TABLE or DROP INDEX. The following are all legal statements for FULLTEXT index manipulation:
CREATE TABLE t (name CHAR(40),
FULLTEXT (name));
ALTER TABLE t ADD FULLTEXT name_idx (name);
ALTER TABLE t DROP INDEX name_idx;
CREATE FULLTEXT INDEX name_idx ON t (name);
DROP INDEX name_idx ON t;
Column prefixes are not applicable for FULLTEXT indexes, which always index entire columns. If you specify a prefix length for a column in a FULLTEXT index, MySQL ignores it.
FULLTEXT index indexes can be constructed on multiple columns, allowing searches to be conducted simultaneously on all the indexed columns. However, leftmost index prefixes are not applicable for FULLTEXT indexes. You must construct one index for every column or combination of columns you want to search. Suppose that you want to search for text sometimes only in column c1 and sometimes in both columns c1 and c2. You must construct two FULLTEXT indexes: one on column c1 and another on columns c1 and c2.
To perform a FULLTEXT search, use MATCH and AGAINST(). For example, to search the table t for records that contain 'Wendell' in the name column, use this query:
SELECT * FROM t WHERE MATCH(name)
AGAINST('Wendell');
The MATCH operator names the column or columns you want to search. As mentioned earlier, there must be a FULLTEXT index on exactly those columns. If you want to search different sets of columns, you'll need one FULLTEXT index for each set. If a table people has name and address columns and you want to search them either separately or together, three FULLTEXT indexes are needed:
CREATE TABLE people
(
name CHAR(40),
address CHAR(40),
FULLTEXT (name), # index for searching
name only
FULLTEXT (address), # index for searching
address only
FULLTEXT (name,address) # index for searching
name and address
);
The indexes allow queries such as the following to be formulated:
SELECT * FROM people WHERE MATCH(name)
AGAINST('string');
SELECT * FROM people WHERE MATCH(address)
AGAINST('string');
SELECT * FROM people WHERE MATCH(name,address)
AGAINST('string');
The preceding discussion summary of FULLTEXT indexing and searching is very brief. More information may be found in the MySQL Reference Manual.
Please check back next week for the continuation of this article.
/ 14 
