Solved

Find unused index on a particular table | MySQL

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

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

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