Today almost all websites have search capability, Using which you can easily search the contents of website by entering some keywords in a text box .So through this post, I will show you how to build a search engine using a keyword based approach. For this, We will use MySQL's extremely powerful full-text search capabilities.
Now you might be wondering about what is Full-Text searching actually?Following points will give you a clear idea about it.
1:- A Full-Text search makes use of full text indexes. In MySQL it is an index of type
Full-Text.These indexes are set up in specific fields of a table. 2:- Full-text indexes can be used only with
MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns. 3:- A
Full Text index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX. 1:- Full-text searches are faster than other search methods such as wildcard or character based searches, which are commonly performed using MySQL's LIKE command.
2:- It is ideal for extremely large databases that contain thousands or even millions of rows. Computations are performed faster and rows can be ranked based on search relevance, which is returned as a decimal number by MySQL.
How it is performed?
Full-text searching is performed using
MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform.Let us take a simple example
First of all we will create a simple database using command create database
Create database manish;
Use manish
Now create a table named data with 3 attributes
CREATE TABLE data (
-> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)Here we have set an index on title and body attribute
mysql> INSERT INTO data (title,body) VALUES
-> ('MySQL Tutorial','DBMS stands for DataBase ...'),
-> ('How To Use MySQL Well','After you went through a ...'),
-> ('Optimizing MySQL','In this tutorial we will show ...');
Now let us perform some search using Match()..Against() Keyword. Here there is an important point to be consider,Because full-text searching was designed for larger databases, it is possible for MySQL to return incorrect results when it's used on tables containing smaller amount records
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
