MySQL FULLTEXT Indexing in database Searching

To define the FULLTEXT index in your MySQL tables, you can search it using MATCH and AGAINST statements to search your database more efficiently.
1) To add FULLTEXT index:

CREATE TABLE blog_entries
(
  entryID INT(9) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
  title CHAR(255) NOT NULL DEFAULT '',
  entry TEXT NOT NULL DEFAULT '',
  FULLTEXT(title,entry)
);

2) Using MATCH and  AGAINST in search:

SELECT title, entry FROM blog_entries
WHERE MATCH (title,entry) AGAINST ('Canada');

Here you  search Canada in title and entry.

If you do not use FULLTEXT index, you can use like in MySQL. But it is slow and inefficient.
Example:
 (SELECT * FROM blog_entries WHERE title LIKE '%Canada%')
UNION
 (SELECT * FROM blog_entries WHERE body LIKE '%Canada%')

3) IN PHP

 <?php

    MySQL_connect("hostname", "username", "password");
    MySQL_select_db("my_db");

    $query = "

       

SELECT title, entry FROM blog_entries
WHERE MATCH (title,entry) AGAINST ('Canada')


    ";

    $sql = MySQL_query($query);

    /* output results */
?>