…foreign key. This is the output of our tool when run on the same table, correctly identifying the redundancy: $ pt-duplicate-key-checker –tables…key-checker, but it does not detect all cases. For example: mysql> alter table…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…table is referencing and have it of proper type and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes…set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) (root@localhost) [oops4]> alter table dos1 change column fkto2 fkto2 char(32) character set…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…table makes any difference: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD KEY (c), ADD KEY…
Post: Converting Character Sets
March 17, 2009 By 22 Comments
…NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET…foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys…
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 5 Comments
…table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table…foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0 rows affected (0.01 sec) mysql…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
…from other transactions’ changes too; the… such as ALTER TABLE in 10k …TABLE: CREATE TABLE load_test ( col1 bigint(20) NOT NULL, col2 bigint(20) default NULL, key(col1), key…mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘infile.txt’ into table…
