… about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our …INDEX_STATS to get the list of unused indexes: mysql> SELECT INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be… In this case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to run queries against your…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…to find the set of indexes in all_indexes, but not in used_indexes. These indexes (if our original index statistics are good) are candidates to…how much disk space an index utilizes. Let’s go update our all_indexes view to include this information: mysql> drop view if exists all_indexes; mysql…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
…time ago about how to find unused indexes with single query. I was working on the system today and found hundreds of unused indexes on dozens …were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt …
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…-index-usage to help you determine how indexes are used in more flexible ways. The default report just prints out ALTER statements for removing unused indexes… SSH tunnel to query EXPLAIN and find out the index usage on the remote server, and store the results in my MySQL sandbox instance…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to…indexes (so it is well possible there are some duplicates, redundant or simply unused indexes…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…INNODB STATUS output and how to use this info to improve MySQL Performance. To start with …unusable. To be honest I do not really like averages Innodb provides as it is hard to get average for interval you want to… `test/parent`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…to decide on couple few questions on how to…to fix – adding couple of proper indexes or tuning couple of MySQL…find them in the situation when they have hard time allocating resources to get application optimized properly – they need to…
