… query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â… 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…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… about 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: 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… to query EXPLAIN and find out the index usage on the remote server, and store the results in my MySQL sandbox instance. The…
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…
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: 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… traversed will not be very long. Another thing you may find unusual is – the key cardinality affects performance even when this…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…last 0 or 1 second they are pretty much unusable. To be honest I do not really like …starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL …But in parent table `test/parent`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…to fix – adding couple of proper indexes or tuning couple of MySQL settings. When low hanging fruits are…immediate replacement may not be available and keeping unused spare box can be expensive. Because fewer users…at later stage. As time goes many project find them in the situation when they have …
