… how to find unused indexes with single query. I was working on the system today and found hundreds of unused indexes on dozens of… were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… index is woeful, but provided that the application is always only sending query 1 to MySQL it’s actually a pretty good index… In this case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have… 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
…unused indexes, which is nice, but it’s often helpful to ask more sophisticated questions about index…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | …
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 … 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…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
I am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just …
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. Unused indexes slow down inserts but they also hurt selects because they waste time in caches. They also make MySQL optimizer to…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches… which again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE… bad to be true, but there is a reason. HASH index stores list of matching values for each hash value. In…
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).
