When to use full text indexes

We are discussing the requirements of a database application with one of them being the ability for users to serch a text field that contains a description of a play in a hockey/basketball game.

For example: Mats Sundin power play goal was his 10th of the year

My impression is that this would be served best by a full text index. An arguement is being made that we can accomplish this by using LIKE to handle the string matching.

My questions are:

1. What are the critieria that would justify a full text index?
2. When are the limitations of LIKE based queries?

We are considering using SQL Server 2000 or MSDE. Possibly MySql to reduce cost and provide Full Text capabilities. The table that will contain the full text index will be adding approximately 100000 records per hockey/basketball season and consist of three columns a foreign key, time and the description.

Thanks for your help in advance.

Jim Received on Fri Jul 04 2003 - 19:33:24 CEST