… table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint ============= Below is scenario. drop table if exists emp; drop table if exists…
Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
February 19, 2013 By 18 Comments
… a foreign key constraint pointing into this table. I tried this: SET FOREIGN_KEY_CHECKS = OFF; ALTER TABLE schema.my_parent_table DROP all foreign keys … ALTER TABLE schema.my_parent_table DROP all indexes … OPTIMIZE…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… 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…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
….h: /* In the pessimistic delete, if the page data size drops below this limit, merging it to a neighbor is tried… a foreign key not reference an entire primary key or unique constraint (something that is mandatory in every other RDBMS that suppors foreign key constraints)? Any concrete examples are appreciated. HT: Yes, it is sufficient that a foreign key and the referenced key appear as…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…drop_eventblogpercona.commanual Com_drop_functionblogpercona.commanual Com_drop_indexblogpercona.commanual Com_drop_procedureblogpercona.commanual Com_drop_serverblogpercona.commanual Com_drop_tableblogpercona.commanual Com_drop…flush_timeblogpercona.commanual foreign_key_checksblogpercona.commanual …
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
… with modified structure, copy data to that table and when drop original table and rename such temporary table to original name… Warnings: 0 SESSION2: root@ubuntu:~/dump# tail -29 dump.sql DROP TABLE IF EXISTS `C`; /*!40101 SET @saved_cs_client = @@character… 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…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
… of a particular task. After those months pass, I’ll drop all those tables, and rebuild them once again for another… KEY (A,B,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…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… use XtraBackup to avoid the server shutdown. You must not drop, truncate or alter the schema of the table after the…” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy the… the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql> SELECT * FROM…
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 an operational overhead. As you will see it… with READ_KEY_RANGE Falcon is the best here. Falcon’s resuts better than InnoDB by 10-30% MyISAM drops down with 128-256 threads READ_KEY_RANGE_LIMIT Query: SELECT name FROM $tableName…
Post: Converting Character Sets
March 17, 2009 By 22 Comments
… multiple alter statements to be run on each table: 1) Drop FULLTEXT indexes 2) Convert target columns to their binary counterparts… include: – Proper handling of string foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys anyway …) – Allow throttling of…
