Using Percona Server 5.5
I've a table which stores user activty (lets call it user_activity). It was created quite a few years ago when I had only a little bit of knowledge about database optimisation. Thusly, I created a few indexes to cope with the different select queries I was likely to run into. Unfortunatly, the indexes are massivly out growning the size of the data and now the table is the largest table I have in my database. Because of this, the more intensive update queries are taking extremly long to run and often cause table locks.
So, I want to see what I can do to improve the table's efficiency in every way and thought I would start with analyzing the indexes we have. Unforutnatly, I can't find a way to determine if an index is needed and with a table this large, i'm not in a position to just remove and re-add as that could take hours to do.
What is the best method to check if an index is needed or can be removed or modified?
Here are the table and index stats:
Engine: InnoDB Row format: Compact Column count: 12 Table Rows: 764,640,132 AVG row length: 66 Data length: 47.3 GB Index length: 168.7GB max data length: 0 bytes data free: 6mb
PRIMARY BTREE (clustered) Unique: Yes Cardinality: 764634204 --------------- actions BTREE Unique: no Cardinality: 18 columns: action_type, action_id --------------- parent BTREE Unique: no Cardinality: 18 columns: section_parent_type, section_parent_id, status, added_date ---------------- Index_parent btree unique: no cardinality: 18 columns: section_parent_type,added_date,user_activity_id ---------------- Index_parent2 btree unique:no cardinality: 18 columns: section_parent_type, section_parent_id,parent_id,parent_type,status,added_date ---------------- Index_actions btree unique:no cardinality:18 columns: section_parent_type, parent_type,parent_id,action_type,added_date
user_activity_id - int(10), notnull, autoinc (primary) user_id - int(10), notnull added_date - datetime action_type - int(10), notnull action_id - int(10), notnull section_parent_type - int(10), notnull section_parent_id - int(10), notnull status - tinyint(3), notnull parent_type - int(10), notnull parent_id - int(10), notnull notification - tinyint(3), notnull friend_activity - tinyint(3), notnull