…mysql> alter table…KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… you create the table with same name again it will need to be able to satisfy foreign key conditions (have column child table is referencing… of proper type and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; Query…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…foreign_key…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; …mysql> DELETE FROM child WHERE 1; — 6 Tickets Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY… query_cache_size=0 skip-name-resolve table…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…mysql> ALTER TABLE t ADD KEY (c), ADD KEY(c); Query OK, 0 rows affected (36.42 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
…TABLE `t1` ( ->Â Â `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE…foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…name. === Error === alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… file names and …MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails for table…tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…(“Select email from user where name=%s”,$name) – > wrapper will …ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table… > Why MySQL Could be slow with Large Tables article. Deciding…/ how to create indexes. Foreign key references may add processing time…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…entries. This has a lot of costs. To name a few: the big LOAD DATA INFILE clogs… rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way; I…mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘infile.txt’ into table…
