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

1 files changed, 23 insertions, 2 deletions

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'},
         ],
     },