0

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:

TABLE:

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

INDEXES:

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

COLUMNS

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
1

Percona themselves have written a tool for this very purpose - it's called pt-index-usage and is to be found here.

From the DESCRIPTION:

This tool connects to a MySQL database server, reads through a query log, and uses EXPLAIN to ask MySQL how it will use each query. When it is finished, it prints out a report on indexes that the queries didn’t use.

Percona's servers, tools, blogs and employees are highly regarded parts of the MySQL ecosystem/community.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.