Making simple search solution

With the advent of web , search is everywhere. To make every contentful application really useful for users, we need to have a good  search capablity inour app to get the right content when users ask for it. My focus here , is to create a pretty simple search solution using the "Full-Text" capability of Sql Server 2005.

Some key features that i belive to watch out.

1. When searching with multiple words, it should not get useless inofrmation. For ex . when we search  "asp.net ajax", it should not return content having "php ajax".
2. Low on CPU usage.
3. Chances of index hit is pretty high.
4. Does have a word index methods for which searching with same keyword is faster.

 Some sqls like we always do

Select * from Person where [Firsname] like '%mehfuz%' or [LastName] like '%hossain%'

First of all , this search is high on IO  and almost neve hits an Index , moreover , it high on cpu usage and really slow.

FullText search on the other hand, ensures that your query hits index, low on logical reads and also faster for searching same keyword. Bascially , To enable fulltext , we need to create a Categlog, that maintains a dictionary for searched keywords, thats why , similar search is always faster.

Now, lets dig into full-text. I have divided this up into some steps, step 1 , 2, and 3, can be done manullay  DB->Stroage - > FullTextCatelogs - > Create New.

Step 1 : Full-Text Initialization

sp_fulltext_database 'enable' - intializes the database for full text search.

Step 2 : Catelog Creation

CREATE FULLTEXT CATALOG <catelogName>

IN PATH N'C:\Database\'

WITH ACCENT_SENSITIVITY = ON

AUTHORIZATION [dbo]

This will create the catelog file under - > DB - > storage -> FullTextCatelog, Accent_sensitivity means  Café(French) and Cafe(English)   will be treated differently.

Step 3 : Index Creation

Add the table and the key(Primay Key, that will be used to track down results) , on which the the search will be made.

CREATE FULLTEXT INDEX ON [dbo].[<TableName>] KEY INDEX [<ClusterIndex Name>] ON [<CatelogName>] WITH CHANGE_TRACKING AUTO

ALTER FULLTEXT INDEX ON [dbo].[<tableName>] ADD ([<Column on which the search will be made>])

ALTER FULLTEXT INDEX ON [dbo].[<tablename>] ENABLE

Step 4  : Query and Result

Select * from <TableName> where Contains([FullTextColumn], 'TV NEAR star')

Contains is a full text routine, That accept keyword LIke 'AND', 'OR' , 'NEAR', you can put columns names in Contains, or put an '*' that will do search on all mapped full text columns.

"NEAR' is useful when used with containsTable  and we want to sort by Rank

Here is the piece of code that i found from MSDN - bit modified

SELECT *

FROM tabletobeSearchOn AS FT_TBL INNER JOIN

CONTAINSTABLE(tabletobeSearchOn , *, 'tv NEAR star') AS KEY_TBL

ON FT_TBL.[ID] = KEY_TBL.[KEY]

ORDER BY KEY_TBL.[Rank] asc

Note :The more closer the search keyword will be ,the more higher wil be the rank(asc).

Now, to make the search more better, i would suggest that  if you have FirstName, lastName, username, sex, columns to be search on , then combine data of all theses columns and dump them to a single Column, Lets say 'FullTextCOlumn' , whenever a change will occur in the table, use trigger to udpate that column with changed data. In this way ur search will get more accurate if you want to search on discreate columns as a whole, or lets say , if you want to simulate a box model.

What is Box model ? Lets say, if you have  'Box1' with balls marked 'Mehfuz', 'Hossain', 'Male' , 'mehfuz@gmail.com' and 'Box2' with balls marked 'Mehfuz', 'Carlos' , 'Male' , calors@newworld.com'. Now if you search for 'Mehfuz Hossain male' , it should get you Box1, not box 2 although there is 'Mehfuz' in FirstName and 'Male in Sex , column of Box2. To get this done the best solution is to combine  columns, in a way that i have just said.

Although, there are plenty of ways to make a search solution , i found the Full-text one more compelling and easy to understand and implement.