…-file-per-table –target-dir=/mnt/mysql/export/ –tables=data # xtrabackup_55 –prepare –export –innodb-file-per-table –target-dir=/mnt/mysql/export ALTER TABLE data DISCARD… extended import of data is being started. InnoDB: Import: 9 indexes have been detected. InnoDB: Progress in %: 1 2 3 4…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… specifying index name ? MySQL will happily create (and maintain) as many duplicate keys as you like without even giving you a warning: mysql> alter table… if you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key idx…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
… [ERROR] Table database_name/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL …doing: ALTER TABLE table_name ENGINE=Innodb; This, of course, rebuilds the whole table based on the .frm table definition and removes the existing index in…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table…mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t… secondary indexes in the table makes any difference: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…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…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… got to learn when working with large data things in MySQL is patience and careful planning. Both of which relate two… about 500GB table for example – ALTER TABLE make take days or even weeks depending on your storage engine and set of indexes, Batch Jobs…’t simply run ALTER TABLE because table will stay locked for too long you would need to do careful process of ALTERing table on the…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… tables which were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… MyISAM tables by separate phase of building Indexes ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any plans to be able to build indexes… patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would like to see…
