We need to write search queries every now and then in our projects. This is one of the most common tasks a PHP developer has to do. Most of the developers still use wildcard queries for this purpose which is very slow in searching records. However MySQL provides an extremely fast alternative to it. We can use the indexed full text search which is way faster than the traditional wild card query.

Full Text Search Syntax: A full text search works on the full text index which is an index of type FULLTEXT. Full text searching has the following syntax :

MATCH() ... AGAINST

The columns to be searched is passed to MATCH() seperated by comma. AGAINST takes a string which is to be searched. AGAINST also takes an optional modifier to specify the type of search to perform. The search string must be a string value that is constant during query evaluation.

Types of Full Text Search : There are three types of full text search:

1. Natural Language Search
2. Boolean search.
3. Query Expansion Search.

We will look into the Boolean and Query Expansion Search in the following parts of this tutorial. For now, lets look into a simple example of natural language search.

How to implement :
Step 1 : Create a sample mysql table, add some data into it.

Step 2 : Make sure that your database table uses MyISAM storage engine, if not then use below query to alter your table.

ALTER TABLE TableName ENGINE=MyISAM;

Step 3 : Create full text index on the columns on which you will need to perform the full text search.

CREATE FULLTEXT INDEX searchindex ON TableName(columnName);

Step 4 : Search

<?php
$search = mysqli_real_escape_string($connection,$search);
$titles = mysqli_query($connection,"SELECT columnName FROM TableName 
    WHERE MATCH(columnName) AGAINST('$search')");
while($row = mysqli_fetch_assoc($titles)) {
    $result[] = $row['columnName'];
}
?>

The above query executes much faster than the traditional wild card queries and will provide more accurate results. You can test it yourself.

Restrictions :
Full text search is faster than wild card search but that doesn't means you can always chose it against wild card queries. There are various reasons and scenarios where full text search queries can/should not be implemented.

1. Full text search are only supported by MyISAM Engine.
2. Full text indexes can be created only for Char, Varchar or Text columns.
3. Full text search can not be applied on the Ideographic languages like Chinese and Japanese as they do not have word delimiters. Due to this unavailability of word delimiters, Full Text Parser cannot determine where words begin and end in these and other such languages. Some implications and workarounds for the same are described in Section 12.9, Full-Text Search Functions.
4. Use of '%' wild card is not supported by Full text Search.

When not to use it :

1. Avoid implementing full text search if the table does not expect search queries too often as indexing has its own overheads.
2. If the table to be searched contains huge number of records.

3. When you need to combine Full Text field and a normal field(say integer) into one index. MySQL will end up using only one index per query.
   Example :

   SELECT * FROM table WHERE MATCH(content) AGAINST('searchQuery') AND status = 1

We will look into other two types in next tutorial.
Happy Programming...