Search code examples
databaseselectfull-text-searchnvarchar

Searching fast in select from a varchar field

I have a database with thousands of records . Each record have a field named "_Text" that contains HTML Code obtained from a web page.

The field type is nvarchar(max).

My problem is searching a word in this table using 'Like' that take a long time to responed because the field have numerous words.

How I can make a fast searching within the '_text' field ??

Solution

  • Thousands of records does not seem like very much. Ideas:

    1. You can create an index for the field where you intend to do the search

    2. You can use fulltext search features

    3. You can use tools, like nginx or elasticsearch

    4. If you have a predefined set of words which might end up in your like parameter, then you can create a keywords field which will contain the keywords

    5. Look at the query you generate. Is it optimal?