…DROP TABLE IF EXISTS `C`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE…KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… foreign key violation error. So Innodb Foreign Keys are pretty good at keeping you hostage. Do you have any better idea how to Hijack innodb Foreign Keys or drop them without rebuilding the table ?
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… tables containing foreign keys as if expand_fast_index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… pessimistic delete, if the page data size drops below this limit, merging it to… In InnoDB, the referenced columns of a foreign key constraint need not form a primary key or …tables are joined to form a single table), it might make sense for a foreign key to reference only a prefix of the primary key…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… must not drop, truncate or alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must… all the InnoDB data we are going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has …key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8 –max-connections=1500 –table…than InnoDB by 10-30% MyISAM drops down with 128-256 threads READ_KEY_…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…foreign_key…innodb_status_fileblogpercona.commanual innodb_strict_modeblogpercona.commanual innodb_support_xablogpercona.commanual innodb_sync_spin_loopsblogpercona.commanual innodb_table_locksblogpercona.commanual innodb_thread_concurrencyblogpercona.commanual innodb…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 139 Comments
… a particular task. After those months pass, I’ll drop all those tables, and rebuild them once again for another couple of…,C,D), KEY (E), CONSTRAINT key_A FOREIGN KEY (A) REFERENCES ATable(A) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT key_B FOREIGN KEY (B) REFERENCES BTable(B) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB; The capital letters…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… 12:09:32 INNODB TABLE MONITOR OUTPUT =========================================== ————————————– TABLE: name SYS_FOREIGN, id 0 11,…0 root page 47, appr.key vals 1, leaf pages 1…table, every column, and every index in that table. Once you get the output, you just DROP the table…
