Mysql Full Text Search – Mysql

In this post you’ll learn how to build a basic full-text search engine using PHP and MySQL.

What is full text search?

The full-text search in mysql allows you to quick and very accurate search 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. Indexed columns can only be TEXT, MySQL does not by default index words that are less than 4 characters. There are also stop words.

Special Features of Full Text Search

  • Full text search is case insensitive.
  • Short words are ignored, the default minimum length is 4 characters.
  • Very common words like “again”, “ also”, “Before”, “Between” etc are stop words, which are ignored.
  • You can disable stopwords by setting the variable in the MySQL configuration.
  • Fulltext searches can only be made on “text” fields.
  • Any word that appears in over 50% of rows is excluded from the results .
  • MySQL requires that you have at least three rows of data in your result set before it will return any results.
  • The search query must be at least four characters long and may not exceed 254 characters.

So 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 name, description column, you will have to run a MySQL query like below on table “Product”.

ALTER TABLE Product ADD FULLTEXT (name, description);

Once you have your index created, you can start performing full-text searches against it.

The basic syntax for a full-SQL query is:

SELECT * from table WHERE MATCH (, ) AGAINST (‘’)

Example Query running on Product table:

SELECT * FROM Product   WHERE MATCH (name, description) AGAINST(“search query”);

The “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 “computer world”, any row that contains either computer or world will be returned.

And here are complete PHP/Mysql full text search scripts that will searches your database.

$query_data = $_GET['query'];
$resultquery = mysql_query(‘SELECT * FROM Product  WHERE MATCH(name, description) AGAINST(“‘.$ query_data.‘”)’);
while($row = mysql_fetch_array($resultquery))
echo $row[name] ;

So here you have learn basics for building a PHP and MySQL driven full-text search engine. Hope it will work for you.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *