347864 - Generally improve fulltext search

Status: RESOLVED FIXED

(Bugzilla :: Query/Bug List, enhancement)

Description

[Max Kanat-Alexander]

As I was working on bug 287170, I noticed that fulltext search could be improved a lot.

For example, let's say you type two words, "crash freeze" into the box. If a summary has the word "crash" in it, but not the word "freeze", it won't show up in the results at all, even if a comment has the word "freeze" in it.

I think there are also some other changes that we could make to improve fulltext search even more. For example, we should just be factoring our relevance on the number of comment matches, not the SUM()/COUNT() way we currently do it, because that doesn't make sense. (You have to read the code and look at the output of the "relevance" column in SQL to understand why it doesn't make sense, but basically: right now if a word appears in two comments, it might actually be ranked *lower* than if it appears in only one comment.)

Comment 1

[Max Kanat-Alexander]

My code for this is going to depend on the code for bug 287170.

Comment 2

[Max Kanat-Alexander]

Attachment: v1

Okay, here we go. On my test installation (I have a large, real dataset--not just landfill's dataset), this gives a somewhat different set of results than our original code.

In order to really know if it works better than our original code, I'd have to ask some people to apply the patch, and then do some typical searches for things they'd actually want to look for, and see if the right things come up in the right order. I have my testing dataset, but it's not on a system I actually *use*, so I don't know if this version of the code is actually *better*.

Comment 3

[Myk Melez [:myk] [@mykmelez]]

Comment on attachment 232714 [details] [diff] [review]
v1

>-               "(SUM($term1)/COUNT(${table}.comment_id) + $term2) AS relevance";
>+               "(SUM($term1) + $term2) AS relevance";

I'm not sure this is better either, but it's worth a shot.


>-        # XXX Perhaps this should be OR, but to duplicate the behavior of 
>-        # bug 251567 I'm making it AND for now.
>-        $clause = join(' AND ', @words);
>+        $clause = join(' OR ', @words);

This seems more obviously like the right thing to do, if we want summary searches to integrate as well as possible into our fuzzy fulltext search system.

Comment 4

[Max Kanat-Alexander]

I had to fix bitrot due to checkin changes on bug 287170. I'll post what actually got checked in.

Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.138; previous revision: 1.137
done

Comment 5

[Max Kanat-Alexander]

Attachment: Checked-In Version