Solved

Find unused index on a particular table | MySQL

Posted on 2012-03-29
4
625 Views
Last Modified: 2012-03-30
Morning -

How can I find the unused indexes on a particular table? I've a table in production which has 23 indexes. The job is to find the unused(useless) and delete them.



root@prod-db2:/# mysql --version;
mysql  Ver 14.14 Distrib 5.5.18, for solaris10 (i386) using readline 5.1
root@prod-db2:/#

Thanks,
Sve.
0
Comment
Question by:sventhan
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
Comment Utility
See http://www.mysqlperformanceblog.com/2009/10/16/how-not-to-find-unused-indexes/

Basically, I think the best way is to use's Percona's Index_Statistics patch.  This allows you to check unused indexes using SQL, with some outer joins.  I've used it myself.

 The other way is to log all queries (set long_query_time to zero), extract the queries, run EXPLAIN on them, take note of all indexes used, then subtract it from all indexes present.  There's a tool described in  http://hackmysql.com/mysqlidxchk that's supposed to do all that, but it's quite old and no longer maintained.

Or, if you know all the queries you run, you can do the same process described above for logged queries.

Note that for all solutions, you have to let it accumulate enough stats (give it enough time to "experience" all queries).  Using the query logging option can generate huge logs, so take note of your disk space.  The percona index statistics patch only uses counters for each index, so not much space is used.
0
 
LVL 18

Author Comment

by:sventhan
Comment Utility
Thanks Johan.

I tried the mysqlidxchk but as you said its not accurate. If you do not mind could you tell me how to apply the percona index patch. I link is not very useful.

Where I can download the patch and how to install?
0
 
LVL 18

Author Closing Comment

by:sventhan
Comment Utility
Thanks and I'll try the patch.
0
 
LVL 24

Expert Comment

by:johanntagle
Comment Utility
The latest patch is at http://bazaar.launchpad.net/~percona-dev/percona-server/5.5.13/view/head:/userstat.patch

Unfortunately, according to http://forum.percona.com/index.php?t=msg&th=1733&start=0, they no longer distribute patches, and they now recommend to use the full percona server build (http://www.percona.com/software/percona-server/).  Sorry about this, I didn't know it  since I'm already using the percona server since last year and haven't had the need to apply the patch.

On the bright side, using the percona server really gives performance benefits, so you might want to consider "upgrading" your instance.  It's a drop-in replacement.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now