Home arrow MySQL arrow Page 7 - Optimizing for Query Speed

13.1.4 FULLTEXT Indexes - MySQL

Optimzing your queries can help them run more efficiently, which can save a significant amount of time. This article covers index optimization and index usage. It is excerpted from chapter 13 of the MySQL Certification Guide, written by Paul Dubois et al. (Sams, 2005; ISBN: 0672328127).

TABLE OF CONTENTS:
  1. Optimizing for Query Speed
  2. 13.1 Index Optimization and Index Usage
  3. 13.1.2 Obtaining Table Index Information
  4. 13.1.3 Using Indexes
  5. 13.1.3.1 Indexing Column Prefixes
  6. 13.1.3.2 Leftmost Index Prefixes
  7. 13.1.4 FULLTEXT Indexes
By: Sams Publishing
Rating: starstarstarstarstar / 25
August 03, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Sams Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: