Hello friends after a long time today I am going to write a post on few of my readers request on How to quick Search in MySQL using full-text indexing in PHP. Many developers make a big mistake during searching from MySQL use wildcard queries “LIKE %string%” which is a very slow query to search records so today we are going to learn full-text indexing search and its hundreds or thousands of times fast then wildcard wildcard queries.

How to get it to work:

1. Create Table:

CREATE TABLE TableName (title CHAR(100));

Insert Data:

INSERT INTO TableName (`title`) VALUES ('MySQL databases are helpful for store data');
INSERT INTO TableName (`title`) VALUES ('PHPGang is a helpful website');
INSERT INTO TableName (`title`) VALUES ('This website is the best');

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;

3. Create full-text index

CREATE FULLTEXT INDEX searchindex ON TableName(title);

4. Search it!

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

This will search records from database more faster then your wildcard queries and show you exact results you want for example:

You have data in your Table and you run below queries:

SELECT * FROM TableName WHERE title LIKE '%helpful website%';

It will return only 1 record.

If you use full-text search it will return any row that matches “helpful” or matches “website”:

SELECT * FROM TableName WHERE MATCH(title) AGAINST ('helpful website');

Boolean mode searching popular with internet search engines – allow you to proceed words with a + or a – to force it to be present (+) or not present (–).

SELECT * FROM TableName WHERE MATCH(title) AGAINST ('helpful -website' IN BOOLEAN MODE);

This Boolean query search only helpful in the search not website as we add – sign before website.

SELECT * FROM TableName WHERE Match(title) AGAINST ('"helpful website"' IN BOOLEAN MODE);

Return only one record as our wildcard query we are forcing it to search complete string in database.

All of these examples are possible Boolean queries:

So this is a simple and very useful tutorial I hope it helps you in your projects and make theme fast and more reliable in search.