…_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_name, INNODB_INDEX…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…unused) indexes from this view: mysql> select * from droppable_indexes; +————–+—————+—————————–+ | table_schema | table_name | index…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…risks . First lets talk about optimizing application vs getting better hardware. With application optimization typically …fix – adding couple of proper indexes or tuning couple of MySQL settings. When low hanging …replacement may not be available and keeping unused spare box can be expensive. Because …
Comment: Dropping unused indexes
January 15, 2009 By 6 Comments
Pat, Unused indexes are dead weight. How much it hurts it is application … discipline dealing with it over years you may get too much of 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… 80 deletes per second while converting table to MyISAM I get about 600 deletes per second and about 4000 deletes per…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… pretty much unusable. To be honest I do not really like averages Innodb provides as it is hard to get average for… 3771312 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id… starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… fit all key blocks from temporary table index, so we get a lot of key writes which kill … 128M ? mysql> show global status like “key%”; +————————+———+ | Variable_name | Value | +————————+———+ | Key_blocks_not_flushed | 49361 | | Key_blocks_unused | 61201 |…
Post: Living with backups
November 6, 2008 By 28 Comments
…happens for as long as it does not get flushed. MySQL obviously takes advantage of this functionality just as… tables which have dedicated buffers only to store indexes, while the actual data is always read from …the cache. It happens by replacing old and unused blocks with the newly read ones. And so…
