… for duplicate keys, but not redundant ones. What is the difference? Let’s see an example. If we execute: mysql> ALTER TABLE test ADD INDEX… mysqlindexcheck, similar to pt-duplicate-key-checker, but it does not detect all cases. For example: mysql> alter table test add index redundant (col2, id…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… warning: mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key(i); Query…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…()),’-',sha1(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table sbtest add key c(c); The box I’m using for…. I used 10mil row table which would look as following in terms of data and index size: mysql> show table status like “sbtest…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…) mysql> alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table | Op… | +——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK, 0 rows affected (5 min 51.83…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… 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 t\G [...] KEY `name` (`name`), KEY `name_2` (`name`), KEY `name_3` (`name`) [...] MySQL…
Post: Improved InnoDB fast index creation
November 6, 2011 By 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 OK, 0 rows affected (36.42 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE t MODIFY…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | | 2 | msandbox | localhost | test | Query | 3 | Waiting for table metadata lock | ALTER TABLE test add column c char(32) default ‘dummy_text’ | | 3 | msandbox… modified table and there will be new entries in the Table Cache. Let’s see this phenomenon in action: session6 > ALTER TABLE test add column… sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
…dumptest > dump.sql SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected… — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_…
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
…simple ALTER TABLE such as: “alter table sbtest add z int not null;” which I expected would create table with primary key …… and indeed it does. mysql> select * from innodb_sys_tables where table_id=18; +———-+——–+————–+——+——–+——-+ | TABLE_ID | SCHEMA | NAME | FLAG …
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…take the employees database, and slightly modify the employees tables: mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB; And…1. row *************************** id: 1 select_type: SIMPLE table: employees type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: …
