Implementing basic searching for your website

2007-09-11

Implementing basic searching for your website using mysql's full text search, and also some tips on how to refine your searches using fulltext boolean search.

I will start with presenting an alternative searching technique that some people use,also pointing its disadvantages.

SELECT * FROM articles WHERE title LIKE '%search text%'

This quick and dirty technique is very simple and very SLOW for tables with lots of rows.It does not break up words from search string, it searches only phrases

The most suitable for searching is mysql's fulltext search

Here is an example on how to use this technique.

First you need to add FULLTEXT index on columns on which you want to search. You cand add your indexes using phpmyadmin.Hhere is my example table with 2 columns that have FULLTEXT indexes, column 'title' which holds the article title, and column 'content' which holds the article content, we want both columns to be searchable.


CREATE TABLE `articles` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL,
  `content` varchar(2048) NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `artistindex` (`artist`)
  FULLTEXT KEY content (`content`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT;

Now that you have added your indexes, you can start searching for articles using the following SQL

SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('Albert Einstein Relativity');

Mysql full text search does not stop here, you can also use boolean modifiers to further refine your searches

SELECT * FROM articles WHERE MATCH (title,content) AGAINST ('+(Albert Einstein) +Relativity' IN BOOLEAN MODE);

In the example above we grouped the two word 'Albert' and 'Einstein' and we added a plus sign + before it to show that these word must be contained in the result. You can search all articles that do not contain a specific word by puting a minus sign before it -

There are also some other boolean search modifiers like '~' that you can add to 'noise words', or '*' for truncation, for example '*nix' will show results for both 'Unix' and 'Linux'

For a complete reference on fulltext boolean search you can read Boolean Full-Text Searches