June 19, 2013

Post: pt-online-schema-change and default values

June 21, 2012 By 1 Comment

…alter=”add column v varchar(100) not null” D=sbtest,t=sbtest Altering `sbtest`.`sbtest`… Creating new table… Created new table sbtest._sbtest_new OK. Altering…

Filed Under: Insight for DBAs, MySQL

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

… auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int… auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild… auto_increment does not work that way. Enum values (add and remove). Enumerated values are added and removed the same way that auto…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…table and there will be new entries in the Table Cache. Let’s see this phenomenon in action: session6 > ALTER TABLE test add… status like ‘Open%tables‘; +—————+——-+ | Variable_name | Value | +—————+——-+ | Open_tables | 30 | | Opened_tables | 0 | +—————+——-+…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , , ,

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…or two capabilities you absolutely need now; then add the rest later, reloading the data if needed…’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-…has a custom table, which is really constructed behind the scenes from the name-value tables in a complex…

Filed Under: Insight for Developers Tagged With: , , , ,

Post: Picking datatype for STATUS fields

August 9, 2008 By 24 Comments

… efficient. The problem with ENUM is adding new values using traditional way (ALTER TABLE) causes table rebuild which is not acceptable for many environments… order in which values are specified in the ENUM declaration – not according to their string values. So make sure to add values specify strings…

Filed Under: Insight for Developers Tagged With:

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 then… then lots of random reads to fetch rows by index value. And random reads are orders of magnitude slower than sequential…. Here if we know that the table will always be in memory, we should add the FORCE INDEX hint to ensure optimal…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… are special cases when this is not the case. CREATE TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a… hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, 0 rows affected… entries being sorted by primary key for each complete key value. So when you have index (a) and id is a…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… I create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0… you defined for the underlying table, or it can be a sequence value generated by InnoDB when the table does not contain a… it, this is what happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Knowing what pt-online-schema-change will do

November 1, 2012 By 1 Comment

…INTO `test`.`_t_new` (`id`, `guest_language`, `guest_country`, `score`) VALUES (NEW.`id`, NEW.`guest_language`, NEW.`guest_country`, NEW.`…the –alter statement: Altering new table… ALTER TABLE `test`.`_t_new` add column (foo char(2)) Altered `test`.`_t_new` OK. If the syntax of the –alter …

Filed Under: Insight for DBAs, Percona Software Tagged With:

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… rows affected (0.00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql… 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…

Filed Under: Benchmarks, Insight for DBAs, MySQL
« Older Posts