… keys requires table rebuild. Yes you get it right even though Innodb is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data change needs a table rebuild… of proper type and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This customer was… the extra index in Innodb could be removed by doing: ALTER TABLE table_name ENGINE=Innodb; This, of course, rebuilds the whole table based on the .frm table definition and removes the existing index in Innodb, which might…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… executed – will in add index live as it can now do on Cluster ? Is it expected to rebuild index by sort of key_cache as it can for MyISAM. EXPLAIN may take long time In MySQL 5.0… some non-index based filtering applied. The other reason for difference is join_buffer which reduces number of passes MySQL had to…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER… table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast index creation. So we have 36 seconds in total which is about 1.7 times faster than updating indexes…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…_index_creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs Fixed InnoDB requires a full table rebuild…851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE query with compound indexes). #832528 (Alexey Kopytov). Added a…
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…_index_creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs Fixed InnoDB requires a full table rebuild… constrains. #859078 (Alexey Kopytov). After adding an index using Fast Index Creation, statistics for that index provided by InnoDB were left in a…
Comment: Why MySQL could be slow with large tables ?
November 29, 2006 By 139 Comments
Normally MySQL is rather fast loading data in MyISAM table, but there is exception, which is when it can’t rebuild indexes by… having large (does not fit in memory) PRIMARY or UNIQUE indexes. Below is the internal letter I’ve sent out on… down to some 100-200 rows/sec as soon as index becomes significantly larger than memory. You can’t go away…
Comment: Why MySQL could be slow with large tables ?
June 17, 2008 By 139 Comments
Normally MySQL is rather fast loading data in MyISAM table, but there is exception, which is when it can’t rebuild indexes by… having large (does not fit in memory) PRIMARY or UNIQUE indexes.
Post: What to do with MySQL Full Text Search while migrating to Innodb ?
September 10, 2009 By 12 Comments
… Search indexes which are not supported for Innodb tables. So what can you do ? Leave Tables as MyISAM The beauty of MySQL… full text search. In certain cases you can just use MySQL triggers to maintain such table, in other cases this would… contention on the writes. In this case you can just rebuild the table from Innodb source on regular basics. In case…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… >= MySQL-4.1.1 and stored already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY… having data corrupted in secondary indexes, in which case simple OPTIMIZE TABLE could be enough to rebuild it, but it is much…) == dict_index_get_page(index)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com…
