sql search for keywords best practice

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

SQL Server 6.5 \ SQL Server 7.0

sql search for keywords best practice

Author

Topic

zeline
Starting Member

2 Posts

Posted - 2008-04-19 : 10:53:18

Hello all,

I have to setup a search engine to look through a list of message. When users are sending their message they enter their title, their message and a list of keywords for their message.

This is perform from a form with the following fields
title, message, keywords

What's the best solution between below 2 options:

1- create one table that hold all datas as below:

table name: messages_table
id | title | message_body | keywords
18 | my title | my message1 | apple, tomato , cooking

and then if I want to look for messages that match with "tomato" and "cooking" i use the following request:

select id,title from messages_table where keywords like "cooking" keywords like "tomato"

2- Create 2 tables
one that hold information about message and one for keywords (one keywords per line)

table1 name: messages_table
id | title | message_body
18 | my title | my message

table2 name: keywords_table
id | id_message | keyword
1 | 18 | apple
2 | 18 | tomato
3 | 18 | cooking

then i have to do a self join if i want to look for "tomato" and "cooking" like this:

select messages_table.id,messages_table.title from messages_table, keywords_table as kwd0, keywords_table as kwd1 where messages_table.id=kwd0.id_message and kwd0.id_message=kwd1.id_message and kwd0.keyword="tomato" and kwd1.keyword="cooking"

Please note that I am using MS SQL server 7.0 , so I don't have the intersect operatot implemented

I hope I have been clear. I propose those 2 solutions but if someone as a third solution he is welcome.

thanks for your help

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-19 : 11:22:53

I would go for option 2.

your query would be
select * from messages_table
where id in
(select id_message
from keywords_table
where keyword in ('tomato','cooking')
group by id_message
having count(distinct keyword) = 2
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

zeline
Starting Member

2 Posts

Posted - 2008-04-19 : 12:39:05

Thanks for your reply,

in terms of performance do you think that the option 2 with a query using "in" and "group by" operator is more efficient than using a like on a text field of keywords ?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-19 : 13:26:36

Yes if you index the Keywords. The csv string can't be indexed usefully.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources