…INDEX `col2`; # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 5 # Total Duplicate Indexes 1 # Total Indexes 3 Additionally, pt-duplicate-key…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… replication. The buffer pool and adaptive hash index on the standby server will not have…username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –dispatcher-plugin thread-pool … no rows or insert statements that have duplicate key errors may be faster than an actual …
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key idx_i(i); ERROR 1061 (42000): Duplicate key name ‘idx_i’ Adding indexes without name specified is very common reason why systems tend to get duplicate indexes…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… index key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add index key_for_first_name(name); ERROR 1061 (42000): Duplicate key name ‘key_for_first_name‘ Using custom names with indexes is a good practice because they can avoid duplicates and…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…Duplicates: 0 Warnings: 0 mysql> SET expand_fast_index_…’t'\G *************************** 1. row *************************** Name: t Engine: InnoDB Version…index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
… what is duplicate index ? This is when table has multiple indexes defined on the same columns. Sometimes it is indexes with different names, sometimes it… 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…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…duplicate indexes. This time we’ll learn how to find unused indexes…NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS.table_name)NOT IN(SELECT CONCAT(index_statistics.index_name, index_statistics.table_name) FROM index…index keys…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… Records: 245760 Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…name, a.constraint_name, a.constraint_type, b.referenced_table_name, b.referenced_column_name UNION SELECT table_schema, table_name, index_name as constraint_name, if(index…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding to the columns in the secondary index…
