… update/insert delete. Duplicate keys are bad so once you find them get rid of them. Note: Duplicate indexes apply to indexes of the same time. It may make sense to have indexes of… index will also be able to use longer index. Unlike with duplicate indexes, there are however cases when redundant indexes are helpful – typically if longer index…
Comment: Redundant index is not always bad
August 28, 2007 By 7 Comments
For those who want to find duplicate indexes, check out MySQL Toolkit: http://mysqltoolkit.sourceforge.net/
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… we refer back to the previous issue’s findings, so that we don’t duplicate efforts from the earlier optimization. Back to… default users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on. As with settings and status…. You cannot consider any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound together, and tweaking…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…) and (a,b) indexes and in the end decides to use neither rather doing full index scan until it finds a=100. This… INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index changes from mk-duplicate-key-checker key checker when it comes to redundant indexes. If you have query…
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… 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: 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/.
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… how to drop duplicate index with same name. === Error === alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid… fk_deptid foreign key(deptid) references dept(deptid) ); * Find below output which shows indexes in the database. (Query for below output is…_column_name UNION SELECT table_schema, table_name, index_name as constraint_name, if(index_type=’FULLTEXT’, ‘FULLTEXT’, ‘NON UNIQUE’) as constraint…
Comment: Duplicate indexes and redundant indexes
March 31, 2008 By 38 Comments
A simple way to find out duplicate indexes in 5.0.x SELECT * FROM information_schema.statistics WHERE … count(column_name) > 1; It would be better to include ‘index_type’ in GROUP BY clause to more accurate answer. In…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test…, 100 rows affected (1.47 sec) Records: 200 Duplicates: 100 Warnings: 0 mysql> insert ignore into test2 …we can do bunch of similar statements to find exact number doing “binary search” Note even …
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… ANALYZE does index scans to find number of…Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index…
