June 19, 2013

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

…Dropping new table… DROP TABLE IF EXISTS `test`.`_t_new`; Dropped new table OK. Notice that the new, altered table created at the beginning is dropped…

Filed Under: Insight for DBAs, Percona Software Tagged With:

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…alter_db_upgradeblogpercona.commanual Com_alter_eventblogpercona.commanual Com_alter_functionblogpercona.commanual Com_alter_procedureblogpercona.commanual Com_alter_serverblogpercona.commanual Com_alter_tableblogpercona.commanual Com_alter…

Filed Under: Insight for DBAs, MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… more detail. ALTER TABLE By temporarily dropping secondary indexes from the new table before copying the data, and then recreating them later, ALTER TABLE can take… a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys as if expand_fast_index_creation is OFF to avoid dropping keys that are…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Comment: Adventures in archiving

February 14, 2013 By 16 Comments

… $table; alter table $table_01 row_format=compressed key_block_size=1; insert into $table_01 select * from $table limit 100000; #drop table if exists $table_02; create table $table_02 like $table; alter table $table_02 row_format=compressed key_block_size=2; insert into $table_02 select * from $table limit 100000; #drop table if exists $table_04; create table $table_04 like $table; alter table $table…

Filed Under: MySQL Tagged With: , ,

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…_4 [...] # To remove this duplicate index, execute: ALTER TABLE `test`.`t` DROP INDEX `name_3`; # Key name ends with a prefix of the clustered… shorten this duplicate clustered index, execute: ALTER TABLE `test`.`t` DROP INDEX `name`, ADD INDEX `name` (`name`); # Key age ends with a prefix of…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… | 229 | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | select…increment primary key) ENGINE=InnoDB; However, I feel that the CREATE TABLE should fail in such a case when the table already exists…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… of adding and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement, but…, this is what happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Best kept MySQLDump Secret

March 23, 2012 By 13 Comments

…ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified structure, copy data to that table and when drop original table… table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES…

Filed Under: Insight for DBAs, MySQL

Comment: Duplicate indexes and redundant indexes

January 19, 2012 By 38 Comments

… === alter 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… | PRIMARY KEY | empid | NULL | NULL | +————–+————+—————–+—————–+————-+———————–+————————+ alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint…

Filed Under: Insight for DBAs Tagged With:

Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

February 19, 2013 By 18 Comments

… FOREIGN_KEY_CHECKS = OFF; ALTER TABLE schema.my_parent_table DROP all foreign keys … ALTER TABLE schema.my_parent_table DROP all indexes … OPTIMIZE TABLE schema.my_parent_table; ALTER TABLE schema.my_parent_table ADD back all indexes and foreign keys…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,
« Older Posts
Newer Posts »