Search Results alter table add index - MySQL Performance Blog

Post: Should you name indexes while doing ALTER TABLE ?

May 28, 2008 By Peter Zaitsev 10 Comments

… not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what might be… 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… Warnings: 0 mysql> alter table t1 add key idx_i(i); ERROR 1061 (42000): Duplicate key name ‘idx_i’ Adding indexes without name specified…

Filed Under: Insight for DBAs Tagged With: Production

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By Aurimas Mikalauskas 26 Comments

… from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id… for table rebuild to complete. If you’re unlucky i.e. you have a lot of indexes and…auto_increment does not work that way. Enum values (add and remove). Enumerated values are added and removed…

Filed Under: Insight for DBAs Tagged With: InnoDB, MyISAM, Tips

Post: Improved InnoDB fast index creation

November 6, 2011 By Alexey Kopytov 4 Comments

… the 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(c); Query… indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Innodb vs MySQL index counts

November 29, 2011 By Jay Janssen 6 Comments

… simple: create an Innodb table make a copy of the .frm file do an ALTER TABLE to add an index then copy the old .frm file back into place re-open the table (Might need a FLUSH TABLES or… 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…

Filed Under: MySQL

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

December 9, 2010 By Peter Zaitsev 13 Comments

… rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK, 0 rows affected (5 min 51… reading table with no indexes and bringing box down. You can also use this trick 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…

Filed Under: Insight for DBAs, MySQL Tagged With: InnoDB, Production, XtraDB

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By Ryan Lowe 4 Comments

… index, a table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table ADD INDEX… Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY (`id`,`parent_id`); — 0 Tickets Used Query…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: Concurrency Tickets, InnoDB, InnoDB Concurrency, innodb_concurrency_tickets, Tuning

Post: Quick tip: how to convert tables to InnoDB

July 30, 2008 By Baron Schwartz 4 Comments

… don’t want to touch. mk-find –engine MyISAM –exec “ALTER TABLE %D.%N ENGINE=INNODB” –print Here’s a bonus tip… index to them — but not to the ones named friends_123_456_user. mk-find –tblregex ‘^user_\d+_\d+_friends$’ –exec ‘ALTER TABLE %D.%N ADD KEY(site_id)’ Boy, is that a lot easier than adding indexes to 90k tables by hand!

Filed Under: Insight for DBAs Tagged With: Tips

Post: How well does your table fits in innodb buffer pool ?

December 9, 2010 By Peter Zaitsev 11 Comments

… Percona Server however adds number of tables to Information Schema…index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_index_stats.index_name AND innodb_index_stats.table_schema = innodb_sys_tables…affected by batch jobs, alter tables, optimize table etc – the lasting…