summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authormkanat%bugzilla.org <>2008-08-13 13:12:41 +0200
committermkanat%bugzilla.org <>2008-08-13 13:12:41 +0200
commita9fd0d7e538ed6c14f4f9b8a855e91393f1b902d (patch)
tree546910c243a74f06c7bc8a7d21aa51e4bb491662
parent09a4647650851390eeef594c6b56ca61d04d6d1b (diff)
downloadbugzilla-a9fd0d7e538ed6c14f4f9b8a855e91393f1b902d.tar.gz
bugzilla-a9fd0d7e538ed6c14f4f9b8a855e91393f1b902d.tar.xz
Bug 442882: Populating bugs_fulltext can be very slow on large installations
Patch By Max Kanat-Alexander <mkanat@bugzilla.org> r=dkl, a=mkanat
-rw-r--r--Bugzilla/DB/Mysql.pm9
-rw-r--r--Bugzilla/Install/DB.pm69
2 files changed, 59 insertions, 19 deletions
diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm
index 43646d8de..d645d79c5 100644
--- a/Bugzilla/DB/Mysql.pm
+++ b/Bugzilla/DB/Mysql.pm
@@ -106,6 +106,15 @@ sub bz_last_key {
return $last_insert_id;
}
+sub sql_group_concat {
+ my ($self, $column, $separator) = @_;
+ my $sep_sql;
+ if ($separator) {
+ $sep_sql = " SEPARATOR $separator";
+ }
+ return "GROUP_CONCAT($column$sep_sql)";
+}
+
sub sql_regexp {
my ($self, $expr, $pattern, $nocheck) = @_;
diff --git a/Bugzilla/Install/DB.pm b/Bugzilla/Install/DB.pm
index 63a6dd2b0..561dc0850 100644
--- a/Bugzilla/Install/DB.pm
+++ b/Bugzilla/Install/DB.pm
@@ -3029,26 +3029,57 @@ sub _populate_bugs_fulltext {
my $bug_ids = $dbh->selectcol_arrayref('SELECT bug_id FROM bugs');
return if !@$bug_ids;
- print "Populating bugs_fulltext.short_desc...\n";
- $dbh->do('INSERT INTO bugs_fulltext (bug_id, short_desc)
- SELECT bug_id, short_desc FROM bugs');
- print "Populating bugs_fulltext comments fields...\n";
- my $count = 1;
- my $sth_all = $dbh->prepare('SELECT thetext FROM longdescs
- WHERE bug_id = ?');
- my $sth_nopriv = $dbh->prepare('SELECT thetext FROM longdescs
- WHERE bug_id = ? AND isprivate = 0');
- my $sth_update = $dbh->prepare(
- 'UPDATE bugs_fulltext SET comments = ?, comments_noprivate = ?
- WHERE bug_id = ?');
- foreach my $id (@$bug_ids) {
- my $all = $dbh->selectcol_arrayref($sth_all, undef, $id);
- my $nopriv = $dbh->selectcol_arrayref($sth_nopriv, undef, $id);
- $sth_update->execute(join("\n", @$all), join("\n", @$nopriv), $id);
- indicate_progress({ total => scalar @$bug_ids, every => 100,
- current => $count++ });
+ # Populating bugs_fulltext can be very slow for large installs,
+ # so we special-case any DB that supports GROUP_CONCAT, which is
+ # a much faster way to do things.
+ if (UNIVERSAL::can($dbh, 'sql_group_concat')) {
+ print "Populating bugs_fulltext...";
+ print " (this can take a long time.)\n";
+ # XXX This hack should probably be moved elsewhere.
+ if ($dbh->isa('Bugzilla::DB::Mysql')) {
+ $dbh->do('SET SESSION group_concat_max_len = 128000000');
+ $dbh->do('SET SESSION max_allowed_packet = 128000000');
+ }
+ $dbh->do(
+ q{INSERT INTO bugs_fulltext (bug_id, short_desc, comments,
+ comments_noprivate)
+ SELECT bugs.bug_id, bugs.short_desc, }
+ . $dbh->sql_group_concat('longdescs.thetext', '\'\n\'')
+ . ', ' . $dbh->sql_group_concat('nopriv.thetext', '\'\n\'') .
+ q{ FROM bugs
+ LEFT JOIN longdescs
+ ON bugs.bug_id = longdescs.bug_id
+ LEFT JOIN longdescs AS nopriv
+ ON longdescs.comment_id = nopriv.comment_id
+ AND nopriv.isprivate = 0 }
+ . $dbh->sql_group_by('bugs.bug_id', 'bugs.short_desc'));
+ }
+ # The slow way, without group_concat.
+ else {
+ print "Populating bugs_fulltext.short_desc...\n";
+ $dbh->do('INSERT INTO bugs_fulltext (bug_id, short_desc)
+ SELECT bug_id, short_desc FROM bugs');
+
+ my $count = 1;
+ my $sth_all = $dbh->prepare('SELECT thetext FROM longdescs
+ WHERE bug_id = ?');
+ my $sth_nopriv = $dbh->prepare(
+ 'SELECT thetext FROM longdescs
+ WHERE bug_id = ? AND isprivate = 0');
+ my $sth_update = $dbh->prepare(
+ 'UPDATE bugs_fulltext SET comments = ?, comments_noprivate = ?
+ WHERE bug_id = ?');
+
+ print "Populating bugs_fulltext comment fields...\n";
+ foreach my $id (@$bug_ids) {
+ my $all = $dbh->selectcol_arrayref($sth_all, undef, $id);
+ my $nopriv = $dbh->selectcol_arrayref($sth_nopriv, undef, $id);
+ $sth_update->execute(join("\n", @$all), join("\n", @$nopriv), $id);
+ indicate_progress({ total => scalar @$bug_ids, every => 100,
+ current => $count++ });
+ }
+ print "\n";
}
- print "\n";
}
}