How do you identify unused indexes in a MySQL database?

views:

answers:

How do you identify unused indexes in a MySQL database?

I have recently completely re-written a large project. In doing so, I have consolidated great number of random MySQL queries. I do remember that over the course of developing the previous codebase, I created indexes on a whim, and I'm sure there are a great number that aren't used anymore.

Is there a way to monitor MySQL's index usage to determine which indexes are being used, and which ones are not?

+4 A:

I don't think this information is available in a stock MySQL installation.

Percona makes tools and patches for MySQL to collect index usage data.

See also:

You may also be interested in a Java app called MySQLIndexAnalyzer, which helps to find redundant indexes. But this tool doesn't have any idea which indexes are unused.

Bill Karwin 2010-07-14 05:10:13

+1 - I'd recommend the Percona patches too. I didn't have access to them, so ended up logging every query run, then parsing this table off-line running EXPLAIN against the queries and counting up which indexes /were/ used, that would then let me figure out which /weren't/!

Dave Rix 2010-07-14 06:27:33

@Dave Rix: Nice solution given the situation. If you also keep a count of how many times each query is seen in your logs, you can map this to the indexes reported by EXPLAIN for each query, and then extrapolate which indexes are used frequently versus infrequently. An index that is used, but used only rarely may also be a candidate for dropping.

Bill Karwin 2010-07-14 06:52:43

@Bill - You're right - it's not available in a stock release, although there is an old tool called mysqlidxchx. See the last two paragraphs of my post here:http://www.mysqlperformanceblog.com/2009/10/16/how-not-to-find-unused-indexes/

Morgan Tocker 2010-07-23 16:20:01

@Morgan: Thanks for the reference!

Bill Karwin 2010-07-23 16:43:31

ansaurus

tags:

views:

answers:

How do you identify unused indexes in a MySQL database?

related questions