… tend to find redundant indexes. And second, because in some cases, we may need to have what at first seems redundant indexes but effectively they are not -for example, if one of the two indexes was unique or a… DROP INDEX `redundant`, ADD INDEX `redundant` (`col2`); # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 9 # Total Duplicate Indexes 1 # Total Indexes 2…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… in index is significant, index (A,B) is not duplicate to index (B,A) So now what are Redundant indexes when ? I call redundant indexes BTREE indexes which are prefix of other index, for example…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part… we use for such queries is covering index, which store all needed columns in the index, and there is no need to… – 470sec (Here I limited available memory to fit only one index, that shows how can degrade performance if we balance on…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…_2` (`name`,`age`) The redundant index is on the ‘name’ column. To take benefit from a composite index MySQL doesn’t need to… long and short index might be good for the query execution. Redundant suffixes on clustered index InnoDB uses a clustered index and that means… solve the problem. Conclusion Indexes are good for our queries but too many indexes or duplicate or redundant indexes can hurt the performance. Checking…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… original index it will quite likely be unable to use new index to full extent. What is solution ? You can have “redundant” indexes on… be very careful applying index changes from mk-duplicate-key-checker key checker when it comes to redundant indexes. If you have query plans depending on Innodb ordering of data by primary key inside indexes they can…
Comment: Redundant index is not always bad
August 29, 2007 By 7 Comments
I mean redundant indexes. Say one with ‘cola’ and another with ‘(cola, colb)’. The … I don’t want to optimize by adding muti-key indexes to every possible combinations of filters a user might have… you have two redundant indexes and you are filtering on ‘cola’ and ‘colb’ it will often choose to use both indexes with merge…
Comment: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…. “Physically” one may have added the shorter index to make for a faster covering index. This is typically not the case — usually… other scenarios for duplicate indexes, such as a UNIQUE KEY (a) and KEY (a,b). There is a redundant index here. Which? See more examples on common_schema’s redundant_keys documentation.
Comment: Duplicate indexes and redundant indexes
September 17, 2006 By 38 Comments
The tool to find duplicate/redundant indexes and foreign keys is on MySQL Forge (http://forge.mysql.com/) and at http://www.xaprb.com/blog/2006/08/28/how-to-find-duplicate-and-redundant-indexes-in-mysql/.
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… only part of the index when full index can be used or using shorter index while there is longer index available. The last item is yet another good reason for removing redundant indexes Here is… looks like as one of its side effects FORCE INDEX actually forces index to be used to largest extent possible: mysql> explain…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with settings and status, these set… big gains. Schema, query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries that run…. You cannot consider any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound together, and tweaking…
