How to Search in MySQL using full-text indexing in PHP
Mysql 26-Feb-2019

How to Search in MySQL using full-text indexing in PHP

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.


3. Create full-text index

CREATE FULLTEXT INDEX searchindex ON TableName(title);

4. Search it!

$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:

helpful website Match either helpful, website, or both
+helpful +website Match both helpful and website
+helpful -website Match helpful but not website
+helpful ~website Match helpful, but mark down as less relevant rows that contain website
+help* Match nice, helpful, helpfully, help website, etc
“helpful website” Match the exact term “nice website”
+helpful +(website blog) Match either “nice website” or “nice blog”
+helpful +(>website <blog) Match either “helpful website” or “helpful blog”, with rows matching “helpful website” being considered more relevant

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.