I'm using the impressionist gem for a Ruby on Rails based website. Our database is mysql.
The impressions table is at about 2M records, which should pose no problem for MySql.
The table size is 800M which is beginning to tax our servers.
The data size of the table is 200M and the index size is 600M. Most of the indexes were predefined by the gem. Is there a way to figure out if any of these indexes are being used or can be deleted?
1,310,855,040 controlleraction_session_index
1,310,855,040 controlleraction_ip_index
1,310,855,040 controlleraction_request_index
--880,757,504 poly_request_index
--880,757,504 impressionable_type_message_index
--880,757,504 poly_session_index
--880,757,504 poly_ip_index
-----6,854,144 PRIMARY
Their README says: "No reporting yet.. this thingy just creates the data."
The indexes are created to speed up the count methods.
@widget.impressionist_count
@widget.impressionist_count(:start_date=>"2011-01-01",:end_date=>"2011-01-05")
It should be safe to remove indexes for all fields that you don't use in your app in these methods.
(note: this answer is from reading their source code for a few minutes. I haven't used the gem)
You can look for duplicate indexes either manually or using the Percona tool (by the way, the documentation for that tool explains what a duplicate index is.
http://www.percona.com/doc/percona-toolkit/2.2/pt-duplicate-key-checker.html
If you're sure an index is a duplicate you can DROP it without ill effects.
Other than than, you need to analyze your query load and figure out whether your queries don't use all the indexes. That's really hard work. You could start by turning on the MySQL general query log for a day or two, or a peak production hour. Then you could analyze the queries in the log. For example, you could use EXPLAIN on the top fifty most frequent queries and see which indexes are being used.
If you do drop certain indexes, you need to pay close attention to your system performance afterwards; you may have missed something important and you'll need to remake them.
But look: we're talking about less than a dollar's worth of disk space here. Maybe $5 if you're using expensive solid state disk drives. If MySQL is slowing down because of the size of these tables, spend your time tuning it rather than trying to save a gigabyte of two of on-disk table space. Maybe you need to adjust internal RAM buffer sizes, or even add an index, or some such thing.
Here's some good material on doing that. http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/
