Macでpercona-toolkitを使う方法

最新号のWEB+DB PRESSで重複indexを検出するためのpercona-toolkitのpt-duplicate-key-checkerが紹介されていました

WEB+DB PRESS Vol.88

Macで簡単にインストールする方法があったので紹介です

忙しい人のまとめ

Homebrew でinstallできる

インストール方法

brew install percona-toolkit

参考URL: http://macappstore.org/percona-toolkit/

pt-duplicate-key-checkerを使ってみる

$  pt-duplicate-key-checker -h localhost -u root -p root -d gitlabhq_test
# ########################################################################
# gitlabhq_test.identities
# ########################################################################

# Key index_identities_on_created_at_and_id ends with a prefix of the clustered index
# Key definitions:
#   KEY `index_identities_on_created_at_and_id` (`created_at`,`id`) USING BTREE,
#   PRIMARY KEY (`id`),
# Column types:
#    `created_at` datetime default null
#    `id` int(11) not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `gitlabhq_test`.`identities` DROP INDEX `index_identities_on_created_at_and_id`, ADD INDEX `index_identities_on_created_at_and_id` (`created_at`);

# ########################################################################
# gitlabhq_test.issues
# ########################################################################

# index_issues_on_project_id is a left-prefix of index_issues_on_project_id_and_iid
# Key definitions:
#   KEY `index_issues_on_project_id` (`project_id`) USING BTREE,
#   UNIQUE KEY `index_issues_on_project_id_and_iid` (`project_id`,`iid`) USING BTREE,
# Column types:
#    `project_id` int(11) default null
#    `iid` int(11) default null
# To remove this duplicate index, execute:
ALTER TABLE `gitlabhq_test`.`issues` DROP INDEX `index_issues_on_project_id`;

便利