… is restarted after the hot test. All OS caches are dropped at this time as well. These set of queries were… ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…duplicate-key-checker, but it does not detect all cases. For example: mysql> alter table test … shorten this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD INDEX `redundant` (`col2`); # ########################################################################…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key… complicated. If you use auto generated index names you may drop the wrong indexes as part of upgrade process just because…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table sbtest add key c(c); The box I’m using… the Index. First Lets look at results of Adding and Dropping column for 128M buffer pool, sized to illustrate the case…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… NULL PRIMARY KEY, j INT NOT NULL, INDEX(j) ) ENGINE=INNODB; trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY… foreign key constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens: (root@localhost) [foobar]> ALTER TABLE bar drop j…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…> alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table | Op… for ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE. P…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… have foreign keys disabled renaming parent table will have reference in child table updated to point to new table. However you can drop parent table without… indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; Query OK, 0 rows affected (0.05 sec) mysql> drop table parentx; Query OK, 0…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 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… recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows affected (24.84…=100 order by id desc limit 1; +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra…
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
…: 1- Create the table First we create the table where we are going to store the information: CREATE TABLE foreign_key_errors ( ts datetime… by the FK, a PRIMARY KEY. So, the command that causes the error was: mysql> ALTER TABLE employees DROP PRIMARY KEY; Conclusion If you are having too many deadlock or foreign key errors log them. Just…
