… table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER… this case InnoDB picks such an index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL bug #49120 Fast Index Creation page…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them … 128MB 17 sec REBUILD 37 sec REBUILD in this table is using “fast_index_creation=0″ which allows to disable fast index creation in… case table rebuild is so close in performance to building index by sort with small buffer – this comes from building index on long…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes …- InnoDB and MyISAM. Now that more and more folks hit the InnoDB auto-inc scalability issue with MySQL …does violate mysql data file structure, so be really careful with that one. Default values. MySQL rebuilds table even…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…people do quite frequently with MySQL 
[+] Compact Indexes Compact Indexes are great. Huge indexes is where large portion of Innodb performance problems come however. We…about this feature, for example changing default value needs table rebuild even though for other storage engines it often could …
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…_fast_index_creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs Fixed InnoDB requires a full table rebuild… as the clustered index by InnoDB in the absence of a PRIMARY KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT=12582913 DEFAULT CHARSET=latin1 mysql> select * from a order by id limit… table is exposed without indexes for some time. Note though nothing stops you from using LOCK TABLES on Innodb table to ensure… no indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes; doing…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name | Value | +——————————+——-+ | Innodb_scan_pages_contiguous | 45 | | Innodb_scan_… it is possible in XtraDB / InnoDB-plugin with FAST INDEX creation) so be careful with …
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… available here and from the Percona Software Repositories). Based on MySQL 5.5.16, including all the bug fixes in it…_fast_index_creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs Fixed InnoDB requires a full table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… of concurrent writes to about 6 tables, what version of Innodb/MySQL would you recommend? I’ve been staying at 4.1… this matter the other day – choosing innodb_buffer_pool_size Q36: There have been several MySQL bugs opened about multi-core scalability… run to show any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may…
Post: Magic Innodb Recovery self healing
May 22, 2007 By 2 Comments
…Innodb, as it is limited to only one particular index on one particular table type it is likely to be Innodb…hoped new MySQL release will have this Innodb bug fixed) until today we got MySQL to … Restarting Innodb with innodb_force_recovery=0 allowed it to start normally and I could rebuild affected …
