Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Find unused index on a particular table | MySQL

Posted on 2012-03-29
4
Medium Priority
?
777 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 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

824 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