Using Relevance Rankings for Full Text and Boolean Searches with MySQL

Introduction

As the above title claims, this series shows you the key points for speeding up the execution of traditional SELECT statements by using the powerful capabilities of full text indexes and Boolean operators. These operators are fully supported by the newest versions of the popular open-source MySQL database server.

Now that you know what this series of educational articles is about, let me provide you with a brief explanation of how full text and Boolean searches work, in case you don't yet have solid background in these helpful features. It's worth keeping in mind that they're not only present in MySQL, but in the majority of production-level database systems, like Oracle and Microsoft SQL Server.

Basically, the implementation of full text indexes for a specific database table implies the usage of the FULL TEXT MySQL command. This command specifies that one or more fields contained in that table will support this feature. This means that when performing a search query against the specified database table, all the searching terms included in the query that happen to be three or more characters long will be directly discarded from it, in this way performing a process commonly known as "noisy words removal."

Logically, as you may have guessed, defining these types of indexes for a given MySQL database table helps to improve the performance of large search queries. As an additional benefit, when the indexes in question are used in conjunction with a MATCH SQL statement, the corresponding results return a relevance ranking in accordance with the search terms included into the query.

Besides, it's very important to mention that full text indexes support the implementation of Boolean operators too, including plus (+) and minus (-) signs, among others. This feature makes it very convenient to specify which search terms should be considered, and which ones should be discarded, when performing a search query against a given database table.

Of course, discussing the numerous benefits in using full text and Boolean searches with MySQL and only covering the theoretical aspect of the subject is a rather pointless process. Therefore in this second part of the series I'm going to demonstrate with several easy-to-grasp examples how to work with relevance rankings, in this way diving a bit deeper into this exciting database-related terrain.

Now, let's get rid of the preliminaries and keep learning more about using full-text and Boolean searches in MySQL. It's going to be instructive, trust me!