Using full-text searches with MySQL - PHP

As I stated in the section that you just read, MySQL supports the use of full-text searches. This can really help speed up the execution of complex queries. But let me give you a brief description of the main features of full-text searches, so you can understand more easily how they work.

In crude terms, to take advantage of full-text searches with MySQL, the database tables used by a specific application must define one or more indexes. These indexes are tied to certain tables' fields, which means the tables in question are structured slightly differently from the conventional way.

Also, full-text searches are considerably faster than traditional searches. This makes them ideal for use with complex and large queries, and allows them to return an additional search relevance value, which will be discussed in detail in further examples.

And finally, full-text searches present a useful feature known popularly as "noisy word removal." This means that any words included in a given search string that have three characters or less will be automatically discarded, in this way accelerating the execution speed of a specific query.

So, this is a brief summary of the most relevant characteristics provided by full-text searches. There are a few more you need to know about, including Boolean operators, that will be covered in the next article of the series.

But now, let me show you an example of how to build a basic MySQL-based search engine, this time using its full-text search capabilities. The first step of this development process is based upon defining the structure of the sample database table that I plan to use here.

In this case, the pertinent sample database table will be called "USERS," and will be created as indicated below:

CREATE TABLE users
(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
  firstname VARCHAR(64),
  lastname VARCHAR(64),
  email VARCHAR(64)
  comments TEXT  
  FULLTEXT(firstname,lastname,comments)
);

As you can see, the above "USERS" database table has been created by defining some basic fields on it, but undeniably its most important characteristic resides on the specification of the respective "firstname," "lastname" and "comments" fields as full-text indexes via the corresponding "FULL TEXT" command.

Now, having at our disposal this useful table, it's possible to built a simple search engine that uses MySQL's full-text capabilities, but first let me populate the prior table with some primitive records, like the ones below:

("users" database table)

Id firstname  lastname           email                              comments

1  Alejandro  Gervasio  alejandro@domain.com  MySQL is great for building a
search engine
2  John          Williams   john@domain.com          PHP is a server side scripting
language
3  Susan       Norton      susan@domain.com       JavaScript is good to manipulate
documents
4  Julie          Wilson      julie@domain.com          MySQL is the best open source
database server