…by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel … XtraBackup would assume the table has been dropped if the tablespace was renamed after … #1176198, bug fixed #1175566. Release notes with all the bugfixes for Percona XtraBackup 2….
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
….table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information… testing allowing to drop all potentially not needed indexes so you can perform proper QA and ensure you really did not drop anything you…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… be dropped: create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index_name from all_indexes…> drop view if exists droppable_indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… use INFORMATION_SCHEMA.INDEX_STATISTICS but first we should enable User Statistics: mysql> SET GLOBAL userstat=on; After all this hard work… reason FK are marked as unused so that means all the other indexes has been used during the benchmark. The second limitation… the impact on performance. You won’t need to drop all such indexes but it gives you an idea of which of them…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… secondary indexes were temporarily dropped and then recreated using fast index creation; dumps produced by mysqldump first create tables with all secondary indexes … As I mentioned previously, InnoDB fast index creation uses temporary files in tmpdir for all indexes being created. So make sure you …
Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
February 19, 2013 By 18 Comments
…; ALTER TABLE schema.my_parent_table DROP all foreign keys … ALTER TABLE schema.my_parent_table DROP all indexes … OPTIMIZE TABLE schema.my_parent_table; ALTER TABLE schema.my_parent_table ADD back all indexes and foreign keys…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… right ! Optimizing table straight away takes over 3 hours, while dropping indexes besides primary key, optimizing table and adding them back takes…, which is close than 20x speed difference and more compact index in the end. So if you’re considering running OPTIMIZE… with no indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes; doing…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… the Index. First Lets look at results of Adding and Dropping column for 128M buffer pool, sized to illustrate the case of index… expand_fast_index_creation=1 which builds table with primary key only when creating a table and when builds all indexes by sort… to the case when you’re adding/dropping/changing columns as if you just add index it will be done by sort…
Comment: Give feedback on a pt-online-schema-change update
May 31, 2012 By 4 Comments
Hi, First, thanks for all these great tools 
, I discover MySQL for only 2 monthes (… it be possible to drop all indexes before copying the data, then recreate all indexes after ? With InnoDB and the fast index creation, it will be…
Comment: Unused indexes by single query
September 15, 2008 By 14 Comments
… of extra indexes can be very large indeed. Of course all the comments on this post are correct, and we really all agree: you have to think carefully about it. Nobody is advocating to just drop all indexes without further thought.
