|
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? |
|
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. "which is not your intention" - good catch! A logic error always trumps performance improvement!
(24 Jul '13, 15:04)
Breck Carter
"text column could be found in the type column" - thanks for drawing the attention to this potential pitfal, anyway it could be easily prevented if you use double quotes for the type column search and structure your type expression in a way, that it is not an expected term from the text column like: MA_DT_3 or similar...
(25 Jul '13, 02:47)
Martin
|
