… an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be… from tablescan to index at about 34%. How am I supposed to find unused indexes then? You really have to run queries against your server… patch in 5.0-percona called INDEX_STATISTICS that can then show you which indexes were touched and which were not. If you are…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
…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…
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 usage. I’ll use this blog’s queries…, make it connect over the SSH tunnel to query EXPLAIN and find out the index usage on the remote server, and store the… with this one: which queries sometimes use different indexes, and what fraction of the time is each index chosen? SELECT iu.query_id, CONCAT_WS…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… wanted to post little query I use for the purpose so I can easily find it later, plus … it to see which tables may be worth to review in terms of indexes. Large index size compared to …a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) …
Comment: Unused indexes by single query
September 14, 2008 By 14 Comments
…you shouldn’t simply drop the unused indexes. This patch, like any patch, should probably only be… the same queries and data as a production server (you could dump the SQL general query log – which will …it. If you want to drop an index, do some performance testing to find out how much benefit (disc …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…index `PRIMARY`, the closest match we can find…which is visible in SHOW PROCESSLIST showed in SHOW INNODB STATUS, such as statement which is being executed, query id, query…index. You can also see number of hash index lookups and number of non-hash index lookups which is indication of hash index…
Post: Economics of Performance Optimization
March 13, 2008 By 9 Comments
…hanging fruits which are easy to fix – adding couple of proper indexes or …may not be available and keeping unused spare box can be expensive. …throughput. If single user CPU bound query takes 30 seconds you unlikely …which may cost you much more to fix at later stage. As time goes many project find…
Post: Give me 8 hours, and I'll help you build a better application
August 29, 2009 By 4 Comments
…essential practices for building successful applications, which everyone should be doing. And…That’s because unused rows can bloat and dilute your indexes and tables…. enough information to help you find the root of the problem….server respond okay to a sample query that your application actually uses?…
