…: mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2…): mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE… with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest” \G… to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement to the index creation speed itself has… having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation it…
Post: Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS
July 1, 2012 By 4 Comments
When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions: One transaction is created when the table being ALTERed is locked…`.`table_name` trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS. Another is created when adding or dropping an index… largest part of ALTER TABLE not being completely crash safe in MySQL is the MySQL server manipulating FRM files. The only MySQL storage engine I…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… multiple indexes on the same column and MySQL won’t complain. Let’s see this example: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create table…
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: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… TABLE bar ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j) ) ENGINE=INNODB; trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j… I think there’s something of an inconsistency present when MySQL won’t allow us to change an FK-referenced column…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…indexes: mysql> SELECT INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS.table…2`; — type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc`.`stock` DROP…
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…
