Solved

Find unused index on a particular table | MySQL

Posted on 2012-03-29
4
645 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
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

19 Experts available now in Live!

Get 1:1 Help Now