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

Martin's gravatar image

Martin
8.6k115149237
accept rate: 14%


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.

permanent link

answered 24 Jul '13, 10:21

Elmi%20Eflov's gravatar image

Elmi Eflov
7811014
accept rate: 31%

"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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×23
×15

question asked: 23 Jul '13, 04:35

question was seen: 1,064 times

last updated: 25 Jul '13, 02:49