… not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what might be… you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will… you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD COLUMN v VARCHAR(1); Query OK, 4194304 rows affected (1… 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…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… as indexes on UUID column or something similar. It also produces a lot better fill factor. The problem is…. OPTIMIZE TABLE for Innodb tables… 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: Redundant index is not always bad
August 28, 2007 By 7 Comments
… is covering index, which store all needed columns in the index, and there is no need to read row data from the table. So – we can extend index `state_id_idx` (`state_id`) by two columns: ALTER TABLE userinfo DROP KEY state…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… (0.00 sec) The obvious optimization is to extend index from column (a) to column (a,b) right which will make it faster and should not hurt any other queries a lot, right ? mysql> alter table idxitest… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | idxitest | index | a | PRIMARY | 4…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
… to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1` text CHARACTER SET… the table and target column, while leaving in place any FULLTEXT indexes. It also requires only a single ALTER statement for a given table. A…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…table as ANALYZE did not run yet. mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… duplicate index with same name. === Error === alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘…table_schema=’test’ GROUP BY table_schema, table_name, constraint_name, constraint_type, referenced_table_name, referenced_column_name ORDER BY table_schema, table_name, column…
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…column…
Comment: Innodb vs MySQL index counts
November 30, 2011 By 6 Comments
…table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL, `data` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB > ALTER TABLE test ADD COLUMN…
