From 620f4ea2be07398a1d0f10293d413c06c44034cf Mon Sep 17 00:00:00 2001 From: "mkanat%kerio.com" <> Date: Tue, 17 May 2005 06:45:27 +0000 Subject: Bug 290677: Index rename time estimate is too short on large sites Patch By Max Kanat-Alexander r=jouni, a=justdave --- Bugzilla/DB/Mysql.pm | 29 +++++++++++++++++++---------- Bugzilla/DB/Schema/Mysql.pm | 24 ++++++++++++++++++++++++ 2 files changed, 43 insertions(+), 10 deletions(-) (limited to 'Bugzilla/DB') diff --git a/Bugzilla/DB/Mysql.pm b/Bugzilla/DB/Mysql.pm index 17c3d6b9e..afa7fe17d 100644 --- a/Bugzilla/DB/Mysql.pm +++ b/Bugzilla/DB/Mysql.pm @@ -260,6 +260,10 @@ sub bz_setup_database { # We estimate one minute for each 3000 bugs, plus 3 minutes just # to handle basic MySQL stuff. my $rename_time = int($bug_count / 3000) + 3; + # And 45 minutes for every 15,000 attachments, per some experiments. + my ($attachment_count) = + $self->selectrow_array("SELECT COUNT(*) FROM attachments"); + $rename_time += int(($attachment_count * 45) / 15000); # If we're going to take longer than 5 minutes, we let the user know # and allow them to abort. if ($rename_time > 5) { @@ -337,6 +341,11 @@ sub bz_setup_database { # Go through all the tables. foreach my $table (@tables) { + # Will contain the names of old indexes as keys, and the + # definition of the new indexes as a value. The values + # include an extra hash key, NAME, with the new name of + # the index. + my %rename_indexes; # And go through all the columns on each table. my @columns = $self->bz_table_columns_real($table); @@ -359,20 +368,20 @@ sub bz_setup_database { foreach my $column (@columns) { # If we have an index named after this column, it's an # old-style-name index. - # This will miss PRIMARY KEY indexes, but that's OK - # because we aren't renaming them. if (my $index = $self->bz_index_info_real($table, $column)) { # Fix the name to fit in with the new naming scheme. - my $new_name = $table . "_" . - $index->{FIELDS}->[0] . "_idx"; - print "Renaming index $column to $new_name...\n"; - # Unfortunately, MySQL has no way to rename an index. :-( - # So we have to drop and recreate the indexes. - $self->bz_drop_index_raw($table, $column, "silent"); - $self->bz_add_index_raw($table, $new_name, - $index, "silent"); + $index->{NAME} = $table . "_" . + $index->{FIELDS}->[0] . "_idx"; + print "Renaming index $column to " + . $index->{NAME} . "...\n"; + $rename_indexes{$column} = $index; } # if } # foreach column + + my @rename_sql = $self->_bz_schema->get_rename_indexes_ddl( + $table, %rename_indexes); + $self->do($_) foreach (@rename_sql); + } # foreach table } # if old-name indexes diff --git a/Bugzilla/DB/Schema/Mysql.pm b/Bugzilla/DB/Schema/Mysql.pm index 21274f575..9ff8822fa 100644 --- a/Bugzilla/DB/Schema/Mysql.pm +++ b/Bugzilla/DB/Schema/Mysql.pm @@ -166,6 +166,30 @@ sub get_drop_index_ddl { return ("DROP INDEX \`$name\` ON $table"); } +# A special function for MySQL, for renaming a lot of indexes. +# Index renames is a hash, where the key is a string - the +# old names of the index, and the value is a hash - the index +# definition that we're renaming to, with an extra key of "NAME" +# that contains the new index name. +# The indexes in %indexes must be in hashref format. +sub get_rename_indexes_ddl { + my ($self, $table, %indexes) = @_; + my @keys = keys %indexes or return (); + + my $sql = "ALTER TABLE $table "; + + foreach my $old_name (@keys) { + my $name = $indexes{$old_name}->{NAME}; + my $type = $indexes{$old_name}->{TYPE}; + $type ||= 'INDEX'; + my $fields = join(',', @{$indexes{$old_name}->{FIELDS}}); + $sql .= " ADD $type $name ($fields), DROP INDEX $old_name,"; + } + # Remove the last comma. + chop($sql); + return ($sql); +} + # Converts a DBI column_info output to an abstract column definition. # Expects to only be called by Bugzila::DB::Mysql::_bz_build_schema_from_disk, # although there's a chance that it will also work properly if called -- cgit v1.2.3-24-g4f1b