I'm using SQL 7 for development but will move to SQL 2K for production.

I have a feature where users can set up a "watch list" of keywords they want to search for. In the original release, they well simply be able to add terms (one at a time) that they want to keep "on watch" and then can go to a page where their terms are displayed with any matching records. In another release, the site will actually e-mail the user when a matching item is made available.

I have two tables for this. One table contains the members ID and the Keyword. The other table has all the items. The search is done on the item name, description and keywords fields.

NOW, what is the most efficient way of doing this? I know that creating a record set of each word, then paging through it and doing numerous searches is not most efficient. (Right now I am limiting the number of watch list searches to 10 unless I come up with a VERY efficient way to allow people to have unlimited watch terms).

The second idea I had was to use the keyworde recordset to make a long search string. Then filter that new recordset so I can display each keyword with matching items below it.

What other way can I do this?

Another question: Does the order of the criteria after WHERE make any difference? For example, in this search, I only want to search items that have an ActiveKey = 1 and a CityID = 1. Should I make sure that is first in order before all the LIKE statements?

Thanks for ANY help you can give.

-Grimps

Phil Grimpo
Executive Director
Inspirmedia