… the same value twice. To accomplish that, InnoDB locks all index records found by the WHERE clause with an exclusive lock… space id 19 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`t` trx id 72C lock_mode… the gap locks except for foreign-key constraint checking or duplicate-key checking. The most important difference between these two options…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… or duplicate indexes so its the time to speak about these a bit. So what is duplicate index ? This is when table has multiple indexes… 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…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… (42000): Duplicate key name ‘idx_i’ Adding indexes without name specified is very common reason why systems tend to get duplicate indexes. BTW make sure you check yours with mk-duplicate…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…); Query OK, 4194304 rows affected (36.07 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SHOW PROFILE; +——————————+———–+ | Status | Duration | +——————————+———–+ | starting | 0… min 15.08 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…); Query OK, 0 rows affected (24.84 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from idxitest where a… 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: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… can “compress” the table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1…; Query OK, 9 rows affected (19.51 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from ex2; +—–+———-+ | val | count(*) | +—–+———-+ | -10… it takes a long time to delete. There is no index on this table. mysql> delete from data where val=16…
Comment: Duplicate indexes and redundant indexes
August 24, 2006 By 38 Comments
… that many third-party tools have duplicate indexes too. Here is one table from Mambo with a duplicate index on a long column: CREATE…
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…_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
August 17, 2006 By 38 Comments
… says about it 
“Note: Duplicate indexes apply to indexes of the same time. It may make sense to have indexes of different types to… on the same column(s) – perfect example is BTREE index and FULLTEXT index, while other combinations may also make sense.” You also might want to have BTREE and RTREE indexes on the same columns or even BTREE and HASH in…
Comment: Redundant index is not always bad
August 28, 2007 By 7 Comments
The main reason I keep around duplicate indexes is because the merge intersect will not use just the first key part of an index. But this also has the disadvantage that it will often use both indexes in the intersect which is a bug in my opinion.
