Hellow Readers ,

Today in my blog I am going to explain about Quick searching in MySQL using FULL-TEXT INDEXING. Many times we made a big mistake during searching from MySQL, we use the wildcard and like operator "LIKE %helpful website%" which is a very slow query to search so today I am going to explain that how you can search data hundreds or thousand of time fast.

First, you need to Create table in DB.

// Make sure that your database table uses MyISAM storage engine.

CREATE TABLE search (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(150)not null,
  created datetime DEFAULT NULL,
  modified datetime DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM

Insert some data in table:

INSERT INTO search (title,created) 
VALUES
('MySQL databases are helpful for store data',NOW()),
('FindNerd is a helpful website',NOW()),
('This website is the best',NOW());

Then Create full-text indexing for search table.

Run this query.

CREATE FULLTEXT INDEX searchindex ON search(title);

Now write search query. This will search data from the database more faster than LIKE Operator.

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

If you run below queries It will return 1 record

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

If you use FULL-TEXT Indexing it will return a row that matches "helpful" or  "website".

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

I hope it helps you in your projects and make theme fast and more reliable in the search.

Feel free to comment.