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.
0 Comment(s)