Speed up SQL Server Fulltext Index through Text Duplication of Non-Indexed Columns
- by Alex
1) I have the text fields FirstName, LastName, and City. They are fulltext indexed.
2) I also have the FK int fields AuthorId and EditorId, not fulltext indexed.
A search on FirstName = 'abc' AND AuthorId = 1 will first search the entire fulltext index for 'abc', and then narrow the resultset for AuthorId = 1.
This is bad because it is a huge waste of resources as the fulltext search will be performed on many records that won't be applicable.
Unfortunately, to my knowledge, this can't be turned around (narrow by AuthorId first and then fulltext-search the subset that matches) because the FTS process is separate from SQL Server.
Now my proposed solution that I seek feedback on: Does it make sense to create another computed column which will be included in the fulltext search which will identify the Author as text (e.g. AUTHORONE). That way I could get rid of the AuthorId restriction, and instead make it part of my fulltext search (a search for 'abc' would be 'abc' and 'AUTHORONE' - all executed as part of the fulltext search).
Is this a good idea or not? Why?