[geeklog-devel] Full-text Search Working

[Tony Bibbs]

Dirk and Crew,

I have modified search.php to make use of MySQL's full text searching 
capabilities.  This implementation was fairly easy.  Here is a quick 
overview of changes I had to make:

1) In config.php I had to add $_DB_full_text_searching which is a 
boolean that enables/disables this.  This feature will only work with 
MySQL 3.23.23 and greater.  If any of you want a small challenge if 
there is a way in MySQL via query to get the MySQL version then we could 
  eliminate a bunch of support hassles by determining this for them

2) I had to do some alter tables:
ALTER TABLE stories ADD FULLTEXT(title,introtext,bodytext);
ALTER TABLE events ADD FULLTEXT(title,description,location);
ALTER TABLE links ADD FULLTEXT(title,description);
ALTER TABLE comments ADD FULLTEXT(title,comment);
NOTE: it will be up to plugin developers to do this for themselves

3) I modified only search.php to do checks against 
$_DB_full_text_searching and build the SQL accordingly.

This is currently working on my site, http://www.iowaoutdoors.org. 
There is only one issue.  The relevancy 'score' is relative to each 
query.  From the MySQL manual:

"Relevance is computed based on the number of words in the row, the 
number of unique words in that row, the total number of words in the 
collection, and the number of documents (rows) that contain a particular 
word."

So if you do a search you'll noticed that the comments on my site get 
scored higher because a separate query was used to pull them.  This gets 
lossed in the fact we combine them with the stories.  My recommendation 
would be that we separate out the comments into it's own block like we 
do for links, events, etc.  Is that Ok?  I thought I'd pose this to the 
group before I starting moving this into CVS.  Dirk, I also wanted to 
give you a chance to play with it to make sure it is stable enough to 
get rolled into your 1.3.7sr1 release which, given Jason's install 
script and this new search functionality, we may want to just call 
1.3.7.1.  Just a suggestion.

Thoughts?  Concerns?

--Tony