| Author |
Topic |
|
cord
Starting Member
7 Posts |
 Posted - 2004-05-24 : 22:01:51
|
| Hello people,I was wondering, when designing an online forum such as this one, would you use a full text search index to process user searches? What kind of hit would you expect? Would you have to make an incremental one and reindex after each post?Generally, I do not know how much of a performance hog full text searching will be which is why I am wondering this.If you would use an alternate method, what would it be?Thanks in advance |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-05-25 : 03:14:46
|
| We use full-text searching on ours, and I can say it performs reasonably well. This site doesn't use full-text indexing as the hosts won't let them do it on a shared server, but graz (the sweet guy who runs this site) has written a cool article on how he really does it: http://www.sqlteam.com/item.asp?ItemID=1876OS |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-05-25 : 03:21:15
|
| I would use a full text search for something like this if I had the chance.I'd probably do something like hourly indexing, depending on the traffic of the site. I suggest doing some experimentation and testing to see if you can get it to work right for you.Damian |
|
|
|
cord
Starting Member
7 Posts |
Posted - 2004-05-25 : 17:27:44
|
| I had two more questions if you dont mind :)1. I belive my host offers full text indexing. However, do you think it is feasible to use it in a shared environment?2. The article you linked to seems to require alot more processing for he algorithm than a full text index would. Am I correct that full text indexing would be a better solution?Thanks |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-05-25 : 19:39:42
|
| Mosts hosts don't consider it something they want to do in a shared environment. If yours will let you, go for it.Full text searching, once the indexing is done, will be much faster than the method in that article.Like I said, you should experiment and test with it to make sure it will do what you want. But for a text heavy application like a forum, Full Text is a very good solution.Damian |
|
|
|
cord
Starting Member
7 Posts |
Posted - 2004-05-26 : 00:13:59
|
| Cool. One last question:I saw that incremental indexing has to be done in some sort of schedule. Is the some way to call it manually everytime a record is updated?Thanks again |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-05-26 : 01:05:30
|
| You could do that by calling sp_fulltext_catalog (look it up in BOL).Damian |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-05-26 : 01:07:27
|
| >>Is the some way to call it manually everytime a record is updated?You really shouldn't do that. If you have a table that gets a lot of writes, it'll become too much of an unnecessary overhead. You'll have to compare your latency requirements against the expected overhead and choose the right balance..you can schedule the incremental indexing a little more often if you are really concerned (say, once every hour or so), that should give a decent balance between the two.OS |
|
|
|
cord
Starting Member
7 Posts |
Posted - 2004-05-26 : 16:09:09
|
| I see.My concern is that pretty much all of the currently (php/mysql) available forums will return search results on new posts on the fly. In other words, as soon as there is a new post, you can search its contents.How much would the overhead of indexing one post at a time be?I guess I could think up some trick to use the full text index to search on the rest of the table and do a regular search on the new topics only? |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-05-27 : 01:01:00
|
Now that sounds like an idea. If you know that your incremental indexing runs every hour, you should be able to do something like this:DECLARE @SearchTable TABLE (PostID INT)INSERT INTO @SearchTable SELECT PostID FROM Forum_Posts WHERE CONTAINS(PostMessage, @SearchTerms)INSERT INTO @SearchTable EXEC MySeachProc @SearchTerms MySearchProc could be an SP like the one graz uses, but it would have a filter to only search through records added in the last one hour. Once you have all the matching PostIDs in the table variable, you can join it back to the relevant tables and retrieve all the information you need.OS |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-05-27 : 01:23:31
|
quote:
Originally posted by cordHow much would the overhead of indexing one post at a time be?I guess I could think up some trick to use the full text index to search on the rest of the table and do a regular search on the new topics only?
If you add a "timestamp" column you can do this, like I said Read BOL, it's in there.Then experiment to see if the indexing time is acceptable.We can't answer too much more, you just gotta give it a shot.Damian |
|
|
|
cord
Starting Member
7 Posts |
Posted - 2004-05-27 : 19:08:24
|
| Yes, I think I have all the information I wanted when I started this thread. I knew about the timestamp column but had not had access to an actual local installation of sql server 2000. Apparently my university offers some free MSDNAA licesnes. I will be looking to acquire a copy through them only thing left is installing windows 2000 or 2003 since it doesnt work on xp :(Thanks alog guys |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-05-28 : 02:43:39
|
| You can install MSDE on Workstation versions of windows and then install the tools - the tools install, illogically, starts with a Server install and a little way in your choose TOOLS ONLY (which will be the only option on a non-server platform).I think there is also a DEVELOPER version of SQL which runs on Workstation O/SKristen |
|
|
|
|
online forum search full text?