check-unused-keys: A tool to interact with INDEX_STATISTICS

June 26, 2009

Author

Ryan Lowe

Insight for DBAs

Share this Post:

With the growing adoption of Google’s User Statistics Patch**, the need for supporting scripts has become clear. To that end, we’ve created check-unused-keys, a Perl script to provide a nicer interface than directly querying the INFORMATION_SCHEMA database.

check-unused-keys can be invoked and used as follows:

<br>%&gt; check-unused-keys --help<br>Usage:<br>     check-unused-keys [OPTIONS]<br><br>     Options:<br>       -d, --databases=&lt;dbname&gt;  Comma-separated list of databases to check<br>       -h, --help                Display this message and exit<br>       -H, --hostname=&lt;hostname&gt; The target MySQL server host<br>       --ignore-databases        Comma-separated list of databases to ignore<br>       --ignore-indexes          Comma-separated list of indexes to ignore<br>                                     db_name.tbl_name.index_name<br>       --ignore-tables           Comma-separated list of tables to ignore<br>                                     db_name.tbl_name<br>       --options-file            The options file to use<br>       -p, --password=&lt;password&gt; The password of the MySQL user<br>       -i, --port=&lt;portnum&gt;      The port MySQL is listening on<br>       -s, --socket=&lt;sockfile&gt;   Use the specified mysql unix socket to connect<br>       -t, --tables=&lt;tables&gt;     Comma-separated list of tables to evaluate<br>                                     db_name.tbl_name<br>       --[no]summary             Display summary information<br>       -u, --username=&lt;username&gt; The MySQL user used to connect<br>       -v, --verbose             Increase verbosity level<br>       -V, --version             Display version information and exit<br><br>     Defaults are:<br><br>     ATTRIBUTE                  VALUE<br>     -------------------------- ------------------<br>     databases                  ALL databases <br>     help                       FALSE<br>     hostname                   localhost<br>     ignore-databases           No default value<br>     ignore-indexes             No default value<br>     ignore-tables              No default value<br>     options-file               ~/.my.cnf<br>     password                   No default value<br>     port                       3306<br>     socket                     No default value<br>     summary                    TRUE<br>     tables                     No Default Value<br>     username                   No default value<br>     verbose                    0 (out of 2)<br>     version                    FALSE<br>

1

%> check-unused-keys --help Usage: check-unused-keys [OPTIONS] Options: -d, --databases=<dbname> Comma-separated list of databases to check -h, --help Display this message and exit -H, --hostname=<hostname> The target MySQL server host --ignore-databases Comma-separated list of databases to ignore --ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name --ignore-tables Comma-separated list of tables to ignore db_name.tbl_name --options-file The options file to use -p, --password=<password> The password of the MySQL user -i, --port=<portnum> The port MySQL is listening on -s, --socket=<sockfile> Use the specified mysql unix socket to connect -t, --tables=<tables> Comma-separated list of tables to evaluate db_name.tbl_name --[no]summary Display summary information -u, --username=<username> The MySQL user used to connect -v, --verbose Increase verbosity level -V, --version Display version information and exit Defaults are: ATTRIBUTE VALUE -------------------------- ------------------ databases ALL databases help FALSE hostname localhost ignore-databases No default value ignore-indexes No default value ignore-tables No default value options-file ~/.my.cnf password No default value port 3306 socket No default value summary TRUE tables No Default Value username No default value verbose 0 (out of 2) version FALSE

** FULLTEXT indexes are not taken into account by this patch, so be wary. And, as always, sanity check the suggestions and test thoroughly before making changes to production.