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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | %> 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.
into maatkit?
@Arjen someday perhaps:)
Does this script work on a standard Mysql installation (without Google’s user_statistics) ?
@petitchevalroux No, it does not. It queries the INFORMATION_SCHEMA.INDEX_STATISTICS table, which is only available with the Google patch set.
I’m pretty sure that is only available with the Percona patches. The Google patches added SHOW commands, we turned them into INFORMATION_SCHEMA tables (I might be wrong about that).
it looks there is a typo
# diff check-unused-keys-0.0.2 check-unused-keys-0.0.2.1
153c153
my @tbls = split(/,/, $OPTIONS{‘tables’});
sorry for the duplicate comment… it looks i cannot paste a diff result here.
but i really think you know what i mean… 🙂
tried to use the below check-ununsed-keys but failed. pls.help
# ./check-unused-keys. -uusername -ppassword -Hhostname -iportname -ddbname
Could not connect to MySQL
i logged in with the same credentials using mysql -uusername -ppassword -hhostname -Pportname
any help will be appreciated
@chava try with -v -v -v and see the debug output
thanks Ryan,
i used perl check-unused-keys. with options and it worked like a charm, we use perl customized to our environment so. i had to use that instead of ./check-unused-keys .
thanks