… query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.Â… case, MySQL flipped from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to run…) and then find someway to parse and EXPLAIN all results, then subtract the indexes that were mentioned from all indexes known. There…
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 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 / … worse – it is hard to find them. But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… query I use for the purpose so I can easily find it later, plus it is quite handy in a way… 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: 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… connect over the SSH tunnel to query EXPLAIN and find out the index usage on the remote server, and store the results…
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).
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…, leave it. If you want to drop an index, do some performance testing to find out how much benefit (disc space, insert…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… for last 0 or 1 second they are pretty much unusable. To be honest I do not really like averages Innodb… ;; But in parent table `test/parent`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 3… hash index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index…
Comment: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
… to rip out unused functionality, sometimes I get carried away 
About PostgreSQL, that’s fine if you have an index to order by and it’s the same index that you’re using to find rows. If the index that’s useful to find…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups… 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…
