MS Sql Full-text search vs. LIKE expression

Question

I'm currently looking for a way to search a big database (500MB - 10GB or more on 10 tables) with a lot of different fields(nvarchars and bigints). Many of the fields, that should be searched are not in the same table.

An example: A search for '5124 Peter' should return all items, that ...

• have an ID with 5124 in it,
• have 'Peter' in the title or description
• have item type id with 5124 in it
• created by a user named 'peter' or a user whose id has 5124 in it
• created by a user with '5124' or 'peter' in his street address.

How should i do the search? I read that the full-text search of MS-Sql is a lot more performant than a query with the LIKE keyword and i think the syntax is more clear, but i think it cant search on bigint(id) values and i read it has performance problems with indexing and therefore slows down inserts to the DB. In my project there will be more inserting than reading, so this could be a matter.

Thanks in advance, Marks

Answer 1

You could try a standalone search engine, such as Sphinx Search:

http://www.sphinxsearch.com/index.html

or Apache Solr:

http://lucene.apache.org/solr/