O'Reilly logo

Expert PHP and MySQL® by Patrick Galbraith, Ronald Bradford, Andrew Curioso

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required