6081
6081
# ###########################################################################
6083
6083
# ###########################################################################
6084
# IndexLength package
6085
# This package is a copy without comments from the original. The original
6086
# with comments and its test file can be found in the Bazaar repository at,
6087
# lib/IndexLength.pm
6088
# t/lib/IndexLength.t
6089
# See https://launchpad.net/percona-toolkit for more information.
6090
# ###########################################################################
6093
package IndexLength;
6096
use warnings FATAL => 'all';
6097
use English qw(-no_match_vars);
6098
use constant PTDEBUG => $ENV{PTDEBUG} || 0;
6101
$Data::Dumper::Indent = 1;
6102
$Data::Dumper::Sortkeys = 1;
6103
$Data::Dumper::Quotekeys = 0;
6106
my ( $class, %args ) = @_;
6107
my @required_args = qw(Quoter);
6108
foreach my $arg ( @required_args ) {
6109
die "I need a $arg argument" unless $args{$arg};
6113
Quoter => $args{Quoter},
6116
return bless $self, $class;
6120
my ($self, %args) = @_;
6121
my @required_args = qw(Cxn tbl index);
6122
foreach my $arg ( @required_args ) {
6123
die "I need a $arg argument" unless $args{$arg};
6125
my ($cxn) = @args{@required_args};
6127
die "The tbl argument does not have a tbl_struct"
6128
unless exists $args{tbl}->{tbl_struct};
6129
die "Index $args{index} does not exist in table $args{tbl}->{name}"
6130
unless $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
6132
my $index_struct = $args{tbl}->{tbl_struct}->{keys}->{$args{index}};
6133
my $index_cols = $index_struct->{cols};
6134
my $n_index_cols = $args{n_index_cols};
6135
if ( !$n_index_cols || $n_index_cols > @$index_cols ) {
6136
$n_index_cols = scalar @$index_cols;
6139
my $vals = $self->_get_first_values(
6141
n_index_cols => $n_index_cols,
6144
my $sql = $self->_make_range_query(
6146
n_index_cols => $n_index_cols,
6149
my $sth = $cxn->dbh()->prepare($sql);
6150
PTDEBUG && _d($sth->{Statement}, 'params:', @$vals);
6151
$sth->execute(@$vals);
6152
my $row = $sth->fetchrow_hashref();
6154
PTDEBUG && _d('Range scan:', Dumper($row));
6155
return $row->{key_len}, $row->{key};
6158
sub _get_first_values {
6159
my ($self, %args) = @_;
6160
my @required_args = qw(Cxn tbl index n_index_cols);
6161
foreach my $arg ( @required_args ) {
6162
die "I need a $arg argument" unless $args{$arg};
6164
my ($cxn, $tbl, $index, $n_index_cols) = @args{@required_args};
6166
my $q = $self->{Quoter};
6168
my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
6169
my $index_cols = $index_struct->{cols};
6170
my $index_columns = join (', ',
6171
map { $q->quote($_) } @{$index_cols}[0..($n_index_cols - 1)]);
6174
foreach my $col ( @{$index_cols}[0..($n_index_cols - 1)] ) {
6175
push @where, $q->quote($col) . " IS NOT NULL"
6178
my $sql = "SELECT /*!40001 SQL_NO_CACHE */ $index_columns "
6179
. "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
6180
. "WHERE " . join(' AND ', @where)
6181
. " ORDER BY $index_columns "
6182
. "LIMIT 1 /*key_len*/"; # only need 1 row
6183
PTDEBUG && _d($sql);
6184
my $vals = $cxn->dbh()->selectrow_arrayref($sql);
6188
sub _make_range_query {
6189
my ($self, %args) = @_;
6190
my @required_args = qw(tbl index n_index_cols vals);
6191
foreach my $arg ( @required_args ) {
6192
die "I need a $arg argument" unless $args{$arg};
6194
my ($tbl, $index, $n_index_cols, $vals) = @args{@required_args};
6196
my $q = $self->{Quoter};
6198
my $index_struct = $tbl->{tbl_struct}->{keys}->{$index};
6199
my $index_cols = $index_struct->{cols};
6202
if ( $n_index_cols > 1 ) {
6203
foreach my $n ( 0..($n_index_cols - 2) ) {
6204
my $col = $index_cols->[$n];
6205
my $val = $vals->[$n];
6206
push @where, $q->quote($col) . " = ?";
6210
my $col = $index_cols->[$n_index_cols - 1];
6211
my $val = $vals->[-1]; # should only be as many vals as cols
6212
push @where, $q->quote($col) . " >= ?";
6214
my $sql = "EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * "
6215
. "FROM $tbl->{name} FORCE INDEX (" . $q->quote($index) . ") "
6216
. "WHERE " . join(' AND ', @where)
6222
my ($package, undef, $line) = caller 0;
6223
@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
6224
map { defined $_ ? $_ : 'undef' }
6226
print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
6231
# ###########################################################################
6232
# End IndexLength package
6233
# ###########################################################################
6235
# ###########################################################################
6084
6236
# This is a combination of modules and programs in one -- a runnable module.
6085
6237
# http://www.perl.com/pub/a/2006/07/13/lightning-articles.html?page=last
6086
6238
# Or, look it up in the Camel book on pages 642 and 643 in the 3rd edition.
6749
6901
else { # chunking the table
6750
6902
if ( $o->get('check-plan') ) {
6751
my $expl = explain_statement(
6752
sth => $statements->{explain_first_lower_boundary},
6903
my $idx_len = new IndexLength(Quoter => $q);
6904
my ($key_len, $key) = $idx_len->index_length(
6907
index => $nibble_iter->nibble_index(),
6908
n_index_cols => $o->get('chunk-index-columns'),
6757
|| lc($expl->{key}) ne lc($nibble_iter->nibble_index()) )
6910
if ( !$key || lc($key) ne lc($nibble_iter->nibble_index()) ) {
6759
6911
die "Cannot determine the key_len of the chunk index "
6760
6912
. "because MySQL chose "
6761
. ($expl->{key} ? "the $expl->{key}" : "no") . " index "
6913
. ($key ? "the $key" : "no") . " index "
6762
6914
. "instead of the " . $nibble_iter->nibble_index()
6763
6915
. " index for the first lower boundary statement. "
6764
6916
. "See --[no]check-plan in the documentation for more "
6765
6917
. "information.";
6767
elsif ( !$expl->{key_len} ) {
6768
die "The key_len of the $expl->{key} index is "
6769
. (defined $expl->{key_len} ? "zero" : "NULL")
6919
elsif ( !$key_len ) {
6920
die "The key_len of the $key index is "
6921
. (defined $key_len ? "zero" : "NULL")
6770
6922
. ", but this should not be possible. "
6771
6923
. "See --[no]check-plan in the documentation for more "
6772
6924
. "information.";
6774
$tbl->{key_len} = $expl->{key_len};
6926
$tbl->{key_len} = $key_len;