Let's use a table with multiple columns one is a long varchar which will be used together with a text index, another column is a standard column which is used to classify the data of the text column. Now a common query will look like:
select * from X where contains(text,'search string') and type='one type'
My question is: What is the best approach for a situation like this - including the type column into the text index and searching like contains(text,type,'search string AND one type') or just the above approach?
asked 23 Jul '13, 04:35
I would expect your original query to be better, as you are not increasing the size of the text index to accommodate the type column. Additionally, if you were to include type column in the text query, it could be found in the text column, and values you are looking for in the text column could be found in the type column, which is not your intention. Performance of the query may benefit from additional index on the type column, but that would need to be tested.
answered 24 Jul '13, 10:21