MySQL's full-text search allows you to quickly search a table for single or multiple keywords from multiple columns. It does this by using an index on the columns that you want to search. There are a few limitations that you should know of. Indexed columns can only be CHAR, VARCHAR, or TEXT, MySQL does not by default index words that are less than 4 characters, and any word that appears in over 50% of rows is excluded from the results. There are also stop words.

With that out of the way, let's start. First you need to create the index. The index should be on the columns that you will be searching. For example, to index the title, body, and tags column, run a MySQL query like this (for the table "posts"):

ALTER TABLE posts ADD FULLTEXT(title, body, tags);
You can also create the index using phpMyAdmin. To do so, browse to the table, click on Structure to edit it, and towards the bottom you'll see something like "Create an index".



Enter the correct number of columns (3 for our example), then click Go. On the next page, enter a name for your index, change the index type to FULLTEXT, and choose the fields that you want to index (leave the size blank). Click save.





Once you have your index created, you can start performing full-text searches against it. To do so, use the "MATCH() AGAINST()" syntax like so:

SELECT * FROM posts WHERE MATCH(title, body, tags) AGAINST("search query");
"Search query" can contain spaces and MySQL will automatically parse them out. By default, it will return any row that contains at least one of the keywords in any of the columns. For example, if the user searches for "mysql tutorial", any row that contains either mysql or tutorial will be returned.

For a really basic search engine, here is what your PHP code might look like:

$qry = $_GET['query'];
$mq = mysql_query('SELECT * FROM posts WHERE MATCH(title, body, tags) AGAINST("'.$qry.'")');
while($row = mysql_fetch_array($mq))
{
echo $row[title] . '<br>';
}

Boolean Mode

To add more functionality to the full-text search, add IN BOOLEAN MODE to the query:

SELECT * FROM posts WHERE MATCH(title, body, tags) AGAINST("query" IN BOOLEAN MODE);
This will allow the use of + and - to require or exclude keywords from the results. For example, you can manually enter pluses via PHP if you want to make the search engine only find results that contain all search words. Or you can leave it up to the users to input these characters to modify their search. For more information on Boolean Mode, see the MySQL docs.