… how to find unused indexes with single query. I was working on the system today and found hundreds of unused indexes on dozens of tables so just dropping indexes manually did not look fun. So… table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA…
Post: Unused indexes by single query
September 12, 2008 By 14 Comments
Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / … find all unused indexes (since last restart of mysqld) by single query SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME… information_schema.index_statistics IST ON CONCAT_WS(‘.’, s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME) = IST.INDEX_NAME WHERE IST.INDEX_NAME…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to… helpful patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not… parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There’s an old tool…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
… determine how indexes are used in more flexible ways. The default report just prints out ALTER statements for removing unused indexes, which is nice, but it’s often helpful to ask more sophisticated questions about index usage. I’ll…’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables | +———————–+ Now let’s run some queries…
Post: check-unused-keys: A tool to interact with INDEX_STATISTICS
June 26, 2009 By 10 Comments
… can be invoked and used as follows: %> check-unused-keys –help Usage: check-unused-keys [OPTIONS] Options: -d, –databases=
Comment: How (not) to find unused indexes
October 16, 2009 By 2 Comments
Dear Morgan, how to add INDEX_STATISTICS on mysql server. I am using mysql 5.1 on Linux server. and my schema contains 90% unused index. Please guide me how to delete unused index? Regards Sudheer G
Comment: Dropping unused indexes
January 15, 2009 By 6 Comments
Pat, Unused indexes are dead weight. How much it hurts it is application … it over years you may get too much of it. Unused indexes slow down inserts but they also hurt selects because they…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
…, 2) idxfrac FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +————————————-+——–+——–+——–+————+———+ | concat(table_schema,’.',table_name) | rows… which tables may be worth to review in terms of indexes. Large index size compared to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may…
Comment: Unused indexes by single query
September 14, 2008 By 14 Comments
Of course you shouldn’t simply drop the unused indexes. This patch, like any patch, should probably only be used … date. It is always safer to leave an index than drop it. If an index is not DEFINITELY causing a problem, leave… disc space, leave it. If you want to drop an index, do some performance testing to find out how much benefit…
Comment: Dropping unused indexes
January 15, 2009 By 6 Comments
Is it possible to find unused index also on a MyISAM table? The above query says “#1109 – Unknown table ‘index_statistics’ in information_schema” at my system (MySQL 5.0.67).
