SQL Server Search 1

[rsshetty]

Hello,

I want to implement a search facility on the relevant columns of my database.

Following is a brief overview of how the data is structured:

Table1 : Message
Columns: Forum_id, Thread_id, Subject, Message, Attachment name

Table 2 : Forum
Columns : Forum_id, Forum Name

Table 3 : Thread
Columns : Thread_id, Thread_name

Table 4 : UploadedCases
Columns : Case_id, Case_detail

Essentially I will have to create a join between the tables Message, Forum and Thread.
The search will have to be performed on the join above and the table - UploadedCases.

I am trying to implement a search where there is one text field and the user enters single/multiple word(s) and I will have to match it with the relevant columns.

The tables - Message and UploadedCases have the actual data and both have about 10000 rows and are still growing.

Any suggestions on the best way to implement this?
Is there a search mechanism where the increasing size of the data will not affect the performance?
I am not sure if implementing Full Text search is an overkill.

Thanks for any input.







rsshetty.
It's always in the details.

 

[vbkris]

>>I am trying to implement a search where there is one text field and the user enters single/multiple word(s) and I will have to match it with the relevant columns.


what all columns must be matched. it looks like u have to use LIKE operator...

Known is handfull, Unknown is worldfull

 

[rsshetty]

The tables mentioned above actually have about 20 columns -some columns with data and some with ID values which need lookup tables.

I may have to match the serach string with all columns of the table. How do I do this?

rsshetty.
It's always in the details.

 

[vbkris]

whoa i gues u have to use something like this:
column1 like '%SEARCH%' and column2 like '%SEARCH%'

can u give me some test data (just a row will do)...

Known is handfull, Unknown is worldfull

 

[rsshetty]

Table: Message

msg_id
forum_id
thread_id
creation_date
modification_date
user_id
subject
msg_body
location_id
.
.
.
There are just too many columns.

Using a like operator would in my opinion slow down the site.



rsshetty.
It's always in the details.

 

[rsshetty]

Would it be possible to create a sql view and search every column of the view..???

Any insights?

rsshetty.
It's always in the details.

 

[micha123]

View would not help with performance problems, on the contrary - unless you have an Index on it (which will be costable for table modifications.

I have some questions here:
1. are you searching for entire words only (or can the user search only some characters of the word, for example: "word encry" instead of "word encrypted"?
2. How much is the table modified (inserts/updates/deletes). Please mention it in a per-second / per-minute or per-day.
3. Is the place of the searched words important? For example: the search for: "I love you"
would bring:
"This is me that says I love you too" OR
"I really really in love with you" OR
"Love you I"?

M.

 

[rsshetty]

Well here goes:

1 - I want to search for entire words. I am willing to forgo partial word matches.
2 - the tables (4-5 in number) are modified approximately 50-100 times daily. The number might increase in the future but not more than 100-200 I would assume.
3 - if I give multiple words, I expect :
"This is me that says I love you too" OR
"I really really in love with you"

,and any record with one of the 3 words. Also, if the user types the pharses in with quotes, I would expect to search records with the exact pharse.



rsshetty.
It's always in the details.

 

[rsshetty]

any ideas?

rsshetty.
It's always in the details.

 

[micha123]

My last question (and crucial for the deployment how could I forget that...) - How long are the strings? are they varchar (8000 characters and less) or text (indefinite)?

And I understand you need to keep the searched words order, but other words can appear in between, correct?
M.

 

[rsshetty]

I hate to complicate this but the fields to be searched are varchar and one text field.

"And I understand you need to keep the searched words order, but other words can appear in between, correct?"

I want to match each word in a phrase to a record, the order is not relevant. If and only if the user types a phrase within double quotes, I will look for the exact phrase in the database tables.
For eg. if I give, I love you
Results:

I went to play today.
I love Thai food.
Do you love me?
And then she said,I love you

If I give, "I love you"
Results:

And then she said,I love you

rsshetty.
It's always in the details.

 

[micha123]

You can use the full-text-search mechanism and issue queries like: I AND love, I OR love....
Then you would have to do some small administration over the indexes when rows in the table are changed or added.

If you do not want to use this mechanism,
I can suggest you another way:

For query performance benefits you can keep another table with the word and its order in the text and more information needed on it. For example:
for your table Message (lets assume there are many different types in there):

create table Message
(msg_id int PRIMARY KEY,
forum_id int
thread_id int
creation_date datetime,
modification_date datetime,
user_id numeric(15,0),
subject varchar(5000),
msg_body varchar(8000),
location_id char(1)....)

I would keep another different table, such as:
create table MessageSearch
(msg_id int,
field_name varchar(128), --I'm not sure if you need it
the_word varchar(8000), -- I don't have solution for text columns...
word_order int)

This table will contain the msg_id (or another PK field to have relation to the Message table) and then each word separately.

If the row in Message is:
msg_id =1,
forum_id=2
thread_id=5
creation_date = '2005-07-06'
modification_date ='2005-07-07'
user_id = 12345678
subject = 'I love you so so so much'
msg_body = 'no message'
location_id = 'C'
...

The MessageSearch will contain:
msg_id field_name the_word word_order
==========================================================
1 msg_id 1 1
1 forum_id 2 1
1 thread_id 5 1
1 creation_date 2005-07-06 1
1 modification_date 2005-07-07 1
1 user_id 12345678 1
1 subject I 1
1 subject love 2
1 subject you 3
1 subject so 4
1 subject so 5
1 subject so 6
1 subject much 7
...

now you can query a join with this table by msg_id as follows:
select *
from Message
where msg_id in (select msg_id from MessageSearch
where the_word = '<the_searched word>')

to be continued...

 

[rsshetty]

definitely creative. What happens if there are 10000 records with 1000-2000 words in each message body?

rsshetty.
It's always in the details.

 

[micha123]

So than it would take a little time to insert the records into the MessageSearch table, but for 100-200 inserts a day, I would still consider this solution.
The MessageSearch table will be big, but with the correct index it might be better than :
col1 like '%search%' and col2 like '%search%' ...
I would test it though.


M.

 

[rsshetty]

Hmm...I think I'll give it a try....

rsshetty.
It's always in the details.