Expert PHP and MySQL®

Chapter 9. Full-Text Searching

WHAT'S IN THIS CHAPTER?

• Understanding and using MySQL FULLTEXT indexes

• Installing, configuring, and using the more efficient Sphinx Full-Text Search Index for full-text searching

• Developing PHP applications that use Sphinx

Searching text is one of the most common functions of a web site and a must-have for RDBMSs. Sometimes, developers will search text in the database using the LIKE operator, but this is very inefficient, especially if there is a large data set involved. This is where full-text search engines become a necessity.

This book covers two means of supporting full-text search functionality using MySQL: FULLTEXT indexes, which are part of the functionality of MySQL, and the Sphinx Full-Text Search Engine, an open-source project that is designed to work well with MySQL.

MYSQL FULLTEXT INDEXES

MySQL supports FULLTEXT indexes (which are pretty much b-tree indexes at least in terms of behavior) that are created against columns containing text. These indexes are built by indexing words found in the text fields using a pointer to the word in the actual location where it exists, thus eliminating stop words such as the, and, and so on. For a complete list of default stop words see http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html.

When the index is used in a search, the search term is matched against the index. The location is known because the index provides a pointer to the text where the term is physically located. Creating a FULLTEXT index ...