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.

Share this post

Comments (10)

  • Arjen Lentz Reply

    into maatkit?

    June 26, 2009 at 3:25 pm
  • Ryan Lowe Reply

    @Arjen someday perhaps:)

    June 26, 2009 at 3:40 pm
  • petitchevalroux Reply

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

    June 27, 2009 at 1:21 am
  • Ryan Lowe Reply

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

    June 27, 2009 at 2:29 pm
  • Baron Schwartz Reply

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

    June 28, 2009 at 6:56 am
  • Lance Li Reply

    it looks there is a typo

    # diff check-unused-keys-0.0.2 check-unused-keys-
    my @tbls = split(/,/, $OPTIONS{‘tables’});

    June 28, 2009 at 8:58 pm
  • Lance Li Reply

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

    but i really think you know what i mean… 🙂

    June 28, 2009 at 9:01 pm
  • chava Reply

    tried to use the below check-ununsed-keys but failed.

    # ./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

    April 23, 2010 at 10:18 am
  • Ryan Lowe Reply

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

    April 23, 2010 at 10:22 am
  • chava Reply

    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 .


    April 23, 2010 at 12:42 pm

Leave a Reply