… 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… auto generated index names you may drop the wrong indexes as part of upgrade process just because somebody was adding custom indexes to the…
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 advantage… 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…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… at 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, s.INDEX_NAME FROM…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
… 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…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It… not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | idxitest | index | a | PRIMARY | 4…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… 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_id…
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…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… to know how to drop duplicate index with same name. === Error === 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…
Comment: Innodb locking and Foreign Keys
August 4, 2008 By 2 Comments
Do You know sth. about altering table with drop foreign key with constraint ?? Because i got only errors. Querys: ALTER TABLE `lpq` drop index `id_lpc`; ALTER TABLE `lpq` DROP FOREIGN KEY id_lpc ; ALTER TABLE `lpq` DROP Index id_lpc ; ALTER TABLE `lpq` DISABLE KEYS And so on. Only Error 150 or 1005
Post: Converting Character Sets
March 17, 2009 By 16 Comments
… multiple alter statements to be run on each table: 1) Drop FULLTEXT indexes 2) Convert target columns to their binary counterparts 3) Convert the table… they need to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1…
