IndexShotgun :fire: :gun: :cop:

Duplicate index checker.

This like pt-duplicate-key-checker, but also supports database other than MySQL

Example

$ index_shotgun postgresql --database=index_shotgun_test
# =============================
# user_stocks
# =============================

# index_user_stocks_on_user_id is a left-prefix of index_user_stocks_on_user_id_and_article_id
# To remove this duplicate index, execute:
ALTER TABLE `user_stocks` DROP INDEX `index_user_stocks_on_user_id`;

# =============================
# user_stocks
# =============================

# index_user_stocks_on_user_id_and_article_id_and_already_read has column(s) on the right side of unique index (index_user_stocks_on_user_id_and_article_id). You can drop if low cardinality
# To remove this duplicate index, execute:
ALTER TABLE `user_stocks` DROP INDEX `index_user_stocks_on_user_id_and_article_id_and_already_read`;

# =============================
# user_stocks
# =============================

# index_user_stocks_on_user_id is a left-prefix of index_user_stocks_on_user_id_and_article_id_and_already_read
# To remove this duplicate index, execute:
ALTER TABLE `user_stocks` DROP INDEX `index_user_stocks_on_user_id`;

# ########################################################################
# Summary of indexes
# ########################################################################

# Total Duplicate Indexes  3
# Total Indexes            6
# Total Tables             5

Requirements

• Ruby 2.3+
• Database you want to use (ex. MySQL, PostgreSQL or SQLite3)

Installation

Add this line to your application's Gemfile:

group :development do
  gem 'index_shotgun'
end

And then execute:

# MySQL
$ bundle install --without oracle postgresql sqlite3

# Oracle
$ bundle install --without mysql postgresql sqlite3

# PostgreSQL
$ bundle install --without mysql oracle sqlite3

# sqlite3
$ bundle install --without mysql oracle postgresql

Or install it yourself as:

$ gem install index_shotgun

If you want to use as commandline tool, you need to install these gems.

# MySQL
$ gem install mysql2

# Oracle
$ gem install activerecord-oracle_enhanced-adapter ruby-oci8

# PostgreSQL
$ gem install pg

# sqlite3
$ gem install sqlite3

Note: requirements activerecord gem v4.2.5+ when using mysql2 gem v0.4.0+

Usage

Ruby app

$ bundle exec rake index_shotgun:fire

run fire :fire: task

If you don't use Rails app, append this to Rakefile

require "index_shotgun/tasks"

Command line

Support these commands

$ index_shotgun
Commands:
  index_shotgun help [COMMAND]                  # Describe available commands or one specific command
  index_shotgun mysql --database=DATABASE       # Search duplicate indexes on MySQL
  index_shotgun oracle --database=DATABASE      # Search duplicate indexes on Oracle
  index_shotgun postgresql --database=DATABASE  # Search duplicate indexes on PostgreSQL
  index_shotgun sqlite3 --database=DATABASE     # Search duplicate indexes on sqlite3
  index_shotgun version                         # Show index_shotgun version

Details: check index_shotgun help <database>

MySQL

$ index_shotgun help mysql
Usage:
  index_shotgun mysql d, --database=DATABASE

Options:
  d, --database=DATABASE
      [--encoding=ENCODING]
                                             # Default: utf8
      [--pool=N]
                                             # Default: 5
  h, [--host=HOST]
                                             # Default: localhost
  P, [--port=N]
                                             # Default: 3306
  u, [--username=USERNAME]
  p, [--password=PASSWORD]
      [--ask-password], [--no-ask-password]

Search duplicate indexes on MySQL

Oracle

$ index_shotgun help oracle
Usage:
  index_shotgun oracle d, --database=DATABASE

Options:
  d, --database=DATABASE
      [--encoding=ENCODING]
                                             # Default: utf8
      [--pool=N]
                                             # Default: 5
  h, [--host=HOST]
                                             # Default: localhost
  P, [--port=N]
                                             # Default: 1521
  u, [--username=USERNAME]
  p, [--password=PASSWORD]
      [--ask-password], [--no-ask-password]

Search duplicate indexes on Oracle

PostgreSQL

$ index_shotgun help postgresql
Usage:
  index_shotgun postgresql d, --database=DATABASE

Options:
  d, --database=DATABASE
      [--encoding=ENCODING]
                                             # Default: utf8
      [--pool=N]
                                             # Default: 5
  h, [--host=HOST]
                                             # Default: localhost
  P, [--port=N]
                                             # Default: 5432
  u, [--username=USERNAME]
  p, [--password=PASSWORD]
      [--ask-password], [--no-ask-password]

Search duplicate indexes on PostgreSQL

SQLite3

$ index_shotgun help sqlite3
Usage:
  index_shotgun sqlite3 d, --database=DATABASE

Options:
  d, --database=DATABASE

Search duplicate indexes on sqlite3

Development

After checking out the repo, run bin/setup to install dependencies. Then, run rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment. Run bundle exec index_shotgun to use the gem in this directory, ignoring other installed copies of this gem.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and tags, and push the .gem file to rubygems.org.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/sue445/index_shotgun.

License

The gem is available as open source under the terms of the MIT License.

FAQ

Q. The origin of the name?

A. Index Shotgun is one of SQL Antipatterns.

https://pragprog.com/book/bksqla/sql-antipatterns