… 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 I extended Vadim’s query to… 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 FROM information_schema.statistics `s` LEFT JOIN information_schema.index…
Post: How (not) to find unused indexes
October 16, 2009 By 2 Comments
… INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should… index is woeful, but provided that the application is always only sending query 1 to MySQL it’s actually a pretty good index… 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: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage’ 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
… directly querying the INFORMATION_SCHEMA database. check-unused-keys can be invoked and used as follows: %> check-unused-keys –help Usage: check-unused-keys… of databases to ignore –ignore-indexes Comma-separated list of indexes to ignore db_name.tbl_name.index_name –ignore-tables Comma-separated…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… 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… make things easier I’ve added INFORMATION_SCHEMA to the query so it works whatever database you have active. It does…
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 … or month, but if you remove the index it relies on to work, its queries will take too long and their job… date. It is always safer to leave an index than drop it. If an index is not DEFINITELY causing a problem, leave…
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).
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…0 or 1 second they are pretty much unusable. To be honest I do not really … such as statement which is being executed, query id, query status etc. Next section you will see is…index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual query_alloc_block_sizeblogpercona.commanual query_cache_limitblogpercona.commanual query_cache_min_res_unitblogpercona.commanual query_cache_sizeblogpercona.commanual query_cache_typeblogpercona.commanual query_cache_wlock_invalidateblogpercona.commanual query_prealloc…
