Solved

Find unused index on a particular table | MySQL

Posted on 2012-03-29
4
695 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37785015
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
ID: 37787251
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
ID: 37787480
Thanks and I'll try the patch.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 37787633
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

734 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