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

    into maatkit?

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

    @Arjen someday perhaps:)

    June 26, 2009 at 3:40 pm
  • petitchevalroux

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

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

    @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

    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

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

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

    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

    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

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

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

    April 23, 2010 at 10:22 am
  • chava

    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

    April 23, 2010 at 12:42 pm

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.