SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL full-text with stemming

    I've put together a solution for adding stemming to MySQL's full-text index searching.

    For those of you unfamiliar with the problem: MySQL's full-text index doesn't recognize word variations, ie, a search for "cats" won't return rows containing the word "cat" and vice versa. A search for "testing" won't return rows containing "test", etc.

    Part 1:
    Add a full-text index the columns you want to search.

    Part 2:
    Get the Porter Stemming class


    Pseudo Code:
    PHP Code:
      include_once("class.stemmer.inc");
      
    $stem = new Stemmer;
      
    $data $stem->stem_list("search_string");
      
    $data implode("* "$data); //add wildcard for boolean search
      
    $data $data."*"// add wildcard to final search string
      
      
    $sql "SELECT *, ((MATCH(col1) AGAINST ('".$data."' IN BOOLEAN MODE))*2)+((MATCH(col2) AGAINST ('".$data."' IN BOOLEAN MODE))) AS score FROM table WHERE MATCH(col1) AGAINST ('".$data."' IN BOOLEAN MODE) and MATCH(col2) AGAINST ('".$data."' IN BOOLEAN MODE) ORDER BY score DESC";
      ... 
    Anybody see any problems with this solution?

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2000
    Location
    Washington, DC
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Upon further testing, this doesn't quite work. "score" doesn't get calculated correctly because IN BOOLEAN MODE doesn't work like that. So, if you don't need score for sorting, then this method for implementing stemming will work.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •