Performing Full-text and Boolean Searches with MySQL

Introduction

These days it's not unusual for developers to find themselves building web sites that use database tables as their back ends. These databases are designed from the very beginning to store plain contents (not HTML output). The popularity of such sites is growing over time with those who build them, since databases allow for easy separation of these contents from their visual presentation.

Naturally, creating maintainable database-driven web sites comes at a cost. The procedure requires the developer to handle a completely separate piece of software, like the database server, in addition to having a basic background in the so-called SQL (Standard Query Language). However, these issues seem to be rather insignificant once a web site is up and running and delivering its database contents quickly and smoothly to different users.

But what happens when the web site in question has grown beyond the expected boundaries and needs an internal search engine? Well, nothing too serious actually. This kind of application can be quickly developed by providing users with a simple web form to enter different search terms, and then implementing the business logic that will return the corresponding results from one or more database tables to visitors.

Nonetheless, the scenario described above can be much more complicated if the hard-coded SQL queries used by the search engine are returning massive amounts of data. This much data can sometimes be irrelevant to certain users, and definitely can consume precious server resources.

In simple terms, can this rather inefficient search engine be improved in some way? Fortunately, the answer to that question is a resounding yes! As you'll possibly know, most modern database servers support the implementation of full-text and Boolean searches. These features can dramatically improve the speed of executed queries and allow users to specify the relevance of certain search terms via simple operators, such as the plus (+) and minus (-) signs, to name the most common ones.

Of course, in this series of articles I'm going to show you how to work with full-text and Boolean searches using MySQL and PHP 5, but the entirety of the code samples that will be developed here can be easily modified to work with a different database server.

Having introduced you to the subject of this series, it's time to move on and discover together the real power of using full-text and Boolean searches with MySQL. Let's begin now!