IndexShotgun :fire: :gun: :cop:
Duplicate index checker.
This like pt-duplicate-key-checker, but also supports database other than MySQL
Example
```sh $ 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.1+
- Database you want to use (ex. MySQL, PostgreSQL or SQLite3)
Installation
Add this line to your application’s Gemfile:
ruby
group :development do
gem 'index_shotgun'
end
And then execute:
```sh # 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:
sh
$ gem install index_shotgun
If you want to use as commandline tool, you need to install these gems.
```sh # 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
sh
$ bundle exec rake index_shotgun:fire
run fire :fire: task
If you don’t use Rails app, append this to Rakefile
ruby
require "index_shotgun/tasks"
Command line
Support these commands
sh
$ 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
```sh $ 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
```sh $ 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
```sh $ 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
```sh $ 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