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

Posted on:



Share Button

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.

Share Button

Ryan Lowe

Ryan was a principal consultant and team manager at Percona until July 2014. He has experience with many database technologies in industries such as health care, telecommunications, and social networking.


, , , ,

Insight for DBAs

  • into maatkit?

  • Ryan Lowe Post author

    @Arjen someday perhaps:)

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

  • Ryan Lowe Post author

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


Leave a Reply