287170 - Need to be able to do fulltext searches on bugs.short_desc even with most tables using InnoDB

Status: RESOLVED FIXED

(Bugzilla :: Creating/Changing Bugs, enhancement)

Description

[Max Kanat-Alexander]

We need to have a MyISAM table for fulltext searches, but InnoDB tables for
transactions.

bugs.short_desc is one of our only fulltext fields, so it needs its own table.

I was thinking that it would become bugs_fulltext.summary. It could also stay
short_desc, because that might make things easier.

Comment 1

[Max Kanat-Alexander]

Bug 96431 is a better way to handle this problem. Then we can remove the
fulltext index.

Comment 2

[Max Kanat-Alexander]

OK, maybe we do want to do it this way, because we still do need the fulltext
index for the specific search.

Comment 3

[Gunnar Wagenknecht]

FWIW, there is a text-search plug-in for MySQL. Although this would be tough to integrate in a database abstract manner.

http://sphinxsearch.com/

Comment 4

[Max Kanat-Alexander]

Okay, I have an idea of how to do this.

Let's modify fulltext search so that it doesn't use a fulltext index on short_desc. Instead, it does this:

1) It splits the input list by spaces and does a short_desc LIKE '%word%' search
   each word.

2) For every word that matches in the summary, we add (some number) to the
   relevance value. I'm thinking of making the "(some number)" 1/$total_words.
   That is, if you typed in three words, two words matching in the summary 
   would add .66 to the relevance. That makes a lot of sense with how we
   currently calculate "relevance" for longdescs.

Comment 5

[Max Kanat-Alexander]

Attachment: v1

Okay, here it is. I went from doing a fulltext match to doing a LIKE match on short_desc.

I was sorely tempted to change the way that fulltext actually *works* during this process, but in the interest of "the patch of least change" I haven't done it. This should return essentially the same results that fulltext search returned before, with perhaps one or two additions (because now we're doing a real substring search in the summary, not just a word search).

This should also fix bug 347721.

Comment 6

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

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

>              # Create search terms to add to the SELECT and WHERE clauses.
>              # $term1 searches comments.
>-             # $term2 searches summaries, which contributes to the relevance
>-             # ranking in SELECT but doesn't limit which bugs get retrieved.
>              my $term1 = $dbh->sql_fulltext_search("${table}.thetext", $v);
>-             my $term2 = $dbh->sql_fulltext_search("bugs.short_desc", $v);
>+             # $term2 searches summaries.
>+             my $term2 = _split_words_into_like("bugs.short_desc", $v);
>+             # For the WHERE clause
>+             my $where_term2 = _split_words_into_like('bugs.short_desc', $v, 1);

Nit: it might be more readable to call the last two variables $term2_select and $term2_where.

Nit: use parentheses consistently (i.e. double-parentheses around "bugs.short_desc" in the second _split_words_into_like call).

Nit: also, factoring out the code into _split_words_into_like is a good idea, but the logical branch based on the third $for_where argument involves relatively little code, and I think the code would be more readable if that branch occurred here rather than in that function.

In other words, have _split_words_into_like return an array of words, then process that array into the select and where clauses here, something like:

             my @words = _split_words_into_like("bugs.short_desc", $v)
             my $term2_where = join(' AND ', @words);
             my $factor = SUMMARY_RELEVANCE_FACTOR;
             my @words = map("CASE WHEN $_ THEN $factor ELSE 0 END", @words);
             my $term2_select = join(' + ', @words);


>+        # XXX Perhaps this should be OR, but to duplicate the behavior of 
>+        # bug 251567 I'm making it AND for now.

Seems to me like it should be OR too.

Comment 7

[Max Kanat-Alexander]

Okay, I fixed all the nits. I'll attach a patch that shows what was actually checked in.

Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.137; previous revision: 1.136
done
Checking in Bugzilla/DB/Schema.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/DB/Schema.pm,v  <--  Schema.pm
new revision: 1.64; previous revision: 1.63
done
Checking in Bugzilla/Install/DB.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Install/DB.pm,v  <--  DB.pm
new revision: 1.10; previous revision: 1.9
don

Comment 8

[Max Kanat-Alexander]

Attachment: Checked-In Version

Comment 9

[Frédéric Buclin]

*** Bug 289207 has been marked as a duplicate of this bug. ***