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.
| 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. |
|
|
|
|
|
|
sql search for keywords best practice