Bug 399370: Fulltext search with a LIKE on bugs.short_desc is too slow (make Bugzilla use a separate fulltext table)

Patch By Max Kanat-Alexander <mkanat@bugzilla.org> (module owner) a=mkanat

4 files changed, 44 insertions, 7 deletions

diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 2da0c44ea..0bd7b7d37 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -255,11 +255,11 @@ EOT
         print "\nISAM->MyISAM table conversion done.\n\n";
     }
 
-    my $sd_index_deleted = 0;
+    my ($sd_index_deleted, $longdescs_index_deleted);
     my @tables = $self->bz_table_list_real();
-    # We want to convert the bugs table to MyISAM, but it's possible that
-    # it has a fulltext index on it and this will fail unless we remove
-    # the index.
+    # We want to convert tables to InnoDB, but it's possible that they have 
+    # fulltext indexes on them, and conversation will fail unless we remove
+    # the indexes.
     if (grep($_ eq 'bugs', @tables)) {
         if ($self->bz_index_info_real('bugs', 'short_desc')) {
             $self->bz_drop_index_raw('bugs', 'short_desc');
@@ -268,6 +268,13 @@ EOT
             $self->bz_drop_index_raw('bugs', 'bugs_short_desc_idx');
             $sd_index_deleted = 1; # Used for later schema cleanup.
         }
+        if ($self->bz_index_info_real('longdescs', 'thetext')) {
+            $self->bz_drop_index_raw('longdescs', 'thetext');
+        }
+        if ($self->bz_index_info_real('longdescs', 'longdescs_thetext_idx')) {
+            $self->bz_drop_index_raw('longdescs', 'longdescs_thetext_idx');
+            $longdescs_index_deleted = 1; # For later schema cleanup.
+        }
     }
 
     # Upgrade tables from MyISAM to InnoDB
@@ -480,6 +487,11 @@ EOT
         $self->_bz_real_schema->delete_index('bugs', 'bugs_short_desc_idx');
         $self->_bz_store_real_schema;
     }
+    if ($longdescs_index_deleted) {
+        $self->_bz_real_schema->delete_index('longdescs', 
+                                             'longdescs_thetext_idx');
+        $self->_bz_store_real_schema;
+    }
 
     # The old timestamp fields need to be adjusted here instead of in
     # checksetup. Otherwise the UPDATE statements inside of bz_add_column
diff --git a/Bugzilla/DB/Pg.pm b/Bugzilla/DB/Pg.pm
index 9675e1f26..4777ba89a 100644
--- a/Bugzilla/DB/Pg.pm
+++ b/Bugzilla/DB/Pg.pm
@@ -179,6 +179,10 @@ sub bz_setup_database {
     # field, because it can't have index data longer than 2770
     # characters on that field.
     $self->bz_drop_index('longdescs', 'longdescs_thetext_idx');
+    # Same for all the comments fields in the fulltext table.
+    $self->bz_drop_index('bugs_fulltext', 'bugs_fulltext_comments_idx');
+    $self->bz_drop_index('bugs_fulltext', 
+                         'bugs_fulltext_comments_noprivate_idx');
 
     # PostgreSQL also wants an index for calling LOWER on
     # login_name, which we do with sql_istrcmp all over the place.
diff --git a/Bugzilla/DB/Schema.pm b/Bugzilla/DB/Schema.pm
index 23f6e0ca4..b8ac649b7 100644
--- a/Bugzilla/DB/Schema.pm
+++ b/Bugzilla/DB/Schema.pm
@@ -279,6 +279,29 @@ use constant ABSTRACT_SCHEMA => {
         ],
     },
 
+    bugs_fulltext => {
+        FIELDS => [
+            bug_id     => {TYPE => 'INT3', NOTNULL => 1, PRIMARYKEY => 1,
+                           REFERENCES => {TABLE  => 'bugs',
+                                          COLUMN => 'bug_id',
+                                          DELETE => 'CASCADE'}},
+            short_desc => {TYPE => 'varchar(255)', NOTNULL => 1},
+            # Comments are stored all together in one column for searching.
+            # This allows us to examine all comments together when deciding
+            # the relevance of a bug in fulltext search.
+            comments   => {TYPE => 'LONGTEXT'},
+            comments_noprivate => {TYPE => 'LONGTEXT'},
+        ],
+        INDEXES => [
+            bugs_fulltext_short_desc_idx => {FIELDS => ['short_desc'],
+                                               TYPE => 'FULLTEXT'},
+            bugs_fulltext_comments_idx   => {FIELDS => ['comments'],
+                                               TYPE => 'FULLTEXT'},
+            bugs_fulltext_comments_noprivate_idx => {
+                FIELDS => ['comments_noprivate'], TYPE => 'FULLTEXT'},
+        ],
+    },
+
     bugs_activity => {
         FIELDS => [
             bug_id    => {TYPE => 'INT3', NOTNULL => 1},
@@ -336,8 +359,6 @@ use constant ABSTRACT_SCHEMA => {
             longdescs_bug_id_idx   => ['bug_id'],
             longdescs_who_idx     => [qw(who bug_id)],
             longdescs_bug_when_idx => ['bug_when'],
-            longdescs_thetext_idx => {FIELDS => ['thetext'],
-                                      TYPE => 'FULLTEXT'},
         ],
     },
 
diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm
index 2f4bc42b2..627716970 100644
--- a/Bugzilla/DB/Schema/Mysql.pm
+++ b/Bugzilla/DB/Schema/Mysql.pm
@@ -86,7 +86,7 @@ use constant REVERSE_MAPPING => {
     # as in their db-specific version, so no reverse mapping is needed.
 };
 
-use constant MYISAM_TABLES => qw(longdescs);
+use constant MYISAM_TABLES => qw(bugs_fulltext);
 
 #------------------------------------------------------------------------------
 sub _initialize {