View Light

Search finally works right!!

Ever since I put in the ability to search messages within MessageBase, it's never worked right. In fact kind of flaky. I'd search for messages containing words that I knew the message contained, and lots of the time I wouldn't find the message at all. If I was lucky, some times I'd find replies to the message, but not the parent message.

It was really confusing and I was thinking that SQL Server 2005 Full-Text Search was just a flaky product.

Well.........

It turned out it was my fault. I feel quite stupid in retrospect.

When you click on Search, it sends your word(s) to a stored procedure running on the database server. It runs, and returns which messages match the search. However, I just found a bug in the code which caused it to drop any message posted by a user who was not logged in (aka Comfortably Anonymous). Which, a lot of the messages I would test the search against happened to be from anonymous users, so I'd never get those messages returned by the search. I fixed the bug, and voila - the searches now seem to work perfectly! :)

For the technically minded out there, I needed to use a LEFT JOIN instead of an INNER JOIN, and had to use the T-SQL ISNULL() function to swap in the words "Comfortably Anonymous" in the case that no user name was found for the message, as happened in the case of an Anonymous User. 

Here's the Before and After code for the stored proc:

BEFORE

SELECT Message.MsgNum, Message.TopicNum, Message.UserNum, Message.MsgTime, Message.Subject, Message.Body, Topic.Subject AS TopicDesc, UserInfo.Name as UserName, Results.Rank
FROM Message
INNER JOIN FREETEXTTABLE(Message, (Body, Subject), @SearchPhrase) AS Results ON Results.[Key] = Message.MsgNum
INNER JOIN Topic ON Message.TopicNum = Topic.TopicNum 
INNER JOIN UserInfo ON Message.UserNum = UserInfo.UserNum
ORDER BY Rank DESC

AFTER (Changes in Green)

SELECT Message.MsgNum, Message.TopicNum, Message.UserNum, Message.MsgTime, Message.Subject, Message.Body, Topic.Subject AS TopicDesc, ISNULL(UserInfo.Name,'Comfortably Anonymous') as UserName, Results.Rank
FROM Message
INNER JOIN FREETEXTTABLE(Message, (Body, Subject), @SearchPhrase) AS Results ON Results.[Key] = Message.MsgNum
INNER JOIN Topic ON Message.TopicNum = Topic.TopicNum
LEFT JOIN UserInfo ON Message.UserNum = UserInfo.UserNum
ORDER BY Rank DESC

Reply
This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available in our efforts to advance understanding of environmental, political, human rights, economic, democracy, scientific, and social justice issues, etc. We believe this constitutes a 'fair use' of any such copyrighted material as provided for in section 107 of the US Copyright Law. In accordance with Title 17 U.S.C. Section 107, the material on this site is distributed without profit to those who have expressed a prior interest in receiving the included information for research and educational purposes. For more information go to: http://www.law.cornell.edu/uscode/17/107.shtml . If you wish to use copyrighted material from this site for purposes of your own that go beyond 'fair use', you must obtain permission from the copyright owner.