…columns/tables based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique‘ (woops!), single column tables, incrementing column…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where used_indexes.INDEX_NAME is NULL and all_indexes.INDEX_NAME != ‘PRIMARY’ and all_indexes.NON_UNIQUE…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… groups by the same column as its sort column. You can optimize by adding the grouping column to your index, so all rows in… its effect on index design? The most important consideration is that “every unique key on the table must use every column in the… your table. Logically, your index design will be the same with a partitioned table as with a non-partitioned table. But since…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc`.`stock` DROP KEY `fkey_stock_2`; — type:non-unique If… with Unique indexes, because maybe they are not used for index lookups but your application may need them to avoid duplicates on the column…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… be indexed, so it is impossible to temporarily drop the corresponding index; mysqldump –innodb-optimize-keys ignores the first UNIQUE index on non-nullable columns when the table has no PRIMARY KEY defined, because in this case InnoDB picks such an index as…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… 0 mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…, index_name as constraint_name, if(index_type=’FULLTEXT’, ‘FULLTEXT’, ‘NON UNIQUE‘) as constraint_type, convert(group_concat(column_name ORDER BY seq_in_index separator ‘, ‘), char) as column_list, null…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
… table with a new column named “foo”. Notice that the table has a PRIMARY key and another non-unique key named “guest_language… questions like: Is the chosen index a good candidate? In this example, it is not: a non-unique key is chosen rather than the PRIMARY key. That signals that the tool has done something wrong. Are all columns selected…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… nice tecnique called Insert Buffer which speeds up inserts for non-unique indexes a lot. However deletes are not handled so they can… least doing it for indexed columns only or even better have option similar to PostgreSQL to specify where this column should be stored… cluster data by index which is not unique – for example cluster data by customer or by timestamp. Creating artificial second column is ugly…
Comment: ORDER BY ... LIMIT Performance Optimization
November 17, 2009 By 100 Comments
…. This is how my indexes are set:- mysql> show index from follow; +——–+————+———-+————–+————–+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub…
