1
# This program is copyright 2008-2011 Percona Inc.
2
# Feedback and improvements are welcome.
4
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
5
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
6
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
8
# This program is free software; you can redistribute it and/or modify it under
9
# the terms of the GNU General Public License as published by the Free Software
10
# Foundation, version 2; OR the Perl Artistic License. On UNIX and similar
11
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
14
# You should have received a copy of the GNU General Public License along with
15
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
16
# Place, Suite 330, Boston, MA 02111-1307 USA.
17
# ###########################################################################
18
# KeySize package $Revision: 7096 $
19
# ###########################################################################
22
# KeySize calculates the size of MySQL indexes (keys).
27
use warnings FATAL => 'all';
28
use English qw(-no_match_vars);
29
use constant MKDEBUG => $ENV{MKDEBUG} || 0;
32
my ( $class, %args ) = @_;
34
return bless $self, $class;
37
# Returns the key's size and the key that MySQL actually chose.
40
# cols => arrayref of key's cols
41
# tbl_name => quoted, db-qualified table name like `db`.`tbl`
42
# tbl_struct => hashref returned by TableParser::parse for tbl
44
# If the key exists in the tbl (it should), then we can FORCE INDEX.
45
# This is what we want to do because it's more reliable. But, if the
46
# key does not exist in the tbl (which happens with foreign keys),
47
# then we let MySQL choose the index. If there's an error, nothing
48
# is returned and you can get the last error, query and EXPLAIN with
49
# error(), query() and explain().
51
my ( $self, %args ) = @_;
52
foreach my $arg ( qw(name cols tbl_name tbl_struct dbh) ) {
53
die "I need a $arg argument" unless $args{$arg};
55
my $name = $args{name};
56
my @cols = @{$args{cols}};
59
$self->{explain} = '';
64
$self->{error} = "No columns for key $name";
68
my $key_exists = $self->_key_exists(%args);
69
MKDEBUG && _d('Key', $name, 'exists in', $args{tbl_name}, ':',
70
$key_exists ? 'yes': 'no');
72
# Construct a SQL statement with WHERE conditions on all key
73
# cols that will get EXPLAIN to tell us 1) the full length of
74
# the key and 2) the total number of rows in the table.
75
# For 1), all key cols must be used because key_len in EXPLAIN only
76
# only covers the portion of the key needed to satisfy the query.
77
# For 2), we have to break normal index usage which normally
78
# allows MySQL to access only the limited number of rows needed
79
# to satisify the query because we want to know total table rows.
80
my $sql = 'EXPLAIN SELECT ' . join(', ', @cols)
81
. ' FROM ' . $args{tbl_name}
82
. ($key_exists ? " FORCE INDEX (`$name`)" : '')
85
foreach my $col ( @cols ) {
86
push @where_cols, "$col=1";
88
# For single column indexes we have to trick MySQL into scanning
89
# the whole index by giving it two irreducible condtions. Otherwise,
90
# EXPLAIN rows will report only the rows that satisfy the query
91
# using the key, but this is not what we want. We want total table rows.
92
# In other words, we need an EXPLAIN type index, not ref or range.
93
if ( scalar @cols == 1 ) {
94
push @where_cols, "$cols[0]<>1";
96
$sql .= join(' OR ', @where_cols);
97
$self->{query} = $sql;
98
MKDEBUG && _d('sql:', $sql);
101
my $sth = $dbh->prepare($sql);
102
eval { $sth->execute(); };
105
$self->{error} = "Cannot get size of $name key: $EVAL_ERROR";
108
$explain = $sth->fetchrow_hashref();
110
$self->{explain} = $explain;
111
my $key_len = $explain->{key_len};
112
my $rows = $explain->{rows};
113
my $chosen_key = $explain->{key}; # May differ from $name
114
MKDEBUG && _d('MySQL chose key:', $chosen_key, 'len:', $key_len,
118
if ( $key_len && $rows ) {
119
if ( $chosen_key =~ m/,/ && $key_len =~ m/,/ ) {
120
$self->{error} = "MySQL chose multiple keys: $chosen_key";
123
$key_size = $key_len * $rows;
126
$self->{error} = "key_len or rows NULL in EXPLAIN:\n"
127
. _explain_to_text($explain);
131
return $key_size, $chosen_key;
134
# Returns the last explained query.
137
return $self->{query};
140
# Returns the last explain plan.
143
return _explain_to_text($self->{explain});
146
# Returns the last error.
149
return $self->{error};
153
my ( $self, %args ) = @_;
154
return exists $args{tbl_struct}->{keys}->{ lc $args{name} } ? 1 : 0;
157
sub _explain_to_text {
158
my ( $explain ) = @_;
160
map { "$_: ".($explain->{$_} ? $explain->{$_} : 'NULL') }
166
my ($package, undef, $line) = caller 0;
167
@_ = map { (my $temp = $_) =~ s/\n/\n# /g; $temp; }
168
map { defined $_ ? $_ : 'undef' }
170
print STDERR "# $package:$line $PID ", join(' ', @_), "\n";
175
# ###########################################################################
176
# End KeySize package
177
# ###########################################################################
added
removed
lib/KeySize.pm
