November 29, 2014

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

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:

** 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.

Comments

  1. into maatkit?

  2. Ryan Lowe says:

    @Arjen someday perhaps:)

  3. Does this script work on a standard Mysql installation (without Google’s user_statistics) ?

  4. Ryan Lowe says:

    @petitchevalroux No, it does not. It queries the INFORMATION_SCHEMA.INDEX_STATISTICS table, which is only available with the Google patch set.

  5. 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).

  6. Lance Li says:

    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’});

  7. Lance Li says:

    sorry for the duplicate comment… it looks i cannot paste a diff result here.

    but i really think you know what i mean… :)

  8. chava says:

    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

  9. Ryan Lowe says:

    @chava try with -v -v -v and see the debug output

  10. chava says:

    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

Speak Your Mind

*