… 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: 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 24 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…
