June 19, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected, 1 warning (0…` (`col2`), # KEY `col2_2` (`col2`,`col3`) # Column types: # `col2` int(11) default null # `col3` varchar(200) default null # To remove this duplicate index… definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types: # `col2` int(11) default null # `id` int(11) not null auto_increment…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Understanding the maximum number of columns in a MySQL table

April 8, 2013 By 5 Comments

… it’s why we can’t have nice things (like default values other than NOW()). It also has certain limits which…(REC_MAX_N_FIELDS – DATA_N_SYS_COLS * 2) Which adds up to (1024-1)-3*2=1017 which is …I was able to hit a too many columns error with only 192 columns (i.e. 191 worked): sub cname ($$) { my $c…

Filed Under: MySQL Tagged With: , , , , , , , ,

Post: Is Synchronous Replication right for your app?

May 14, 2013 By 18 Comments

… someone joins a group, you run a transaction that adds the relationship row to users_groups, but also …only the number of times specified by this variable (default is 1 retry).  This means more waiting, and … Example 2, above, how above moving the ‘joined’ column to the users_groups table so we don’…

Filed Under: MySQL, Percona Software, XtraDB Cluster Tagged With: , , , , , ,

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

June 21, 2012 By 1 Comment

… can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even though we do not specify default value. MySQL will assign empty string as default default…

Filed Under: Insight for DBAs, MySQL

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… | Duration | Query | +———-+————-+————————————————————-+ | 1 | 46.78110075 | ALTER TABLE test add column c char(32) default ‘dummy_text’ | +———-+————-+————————————————————-+ 1 row in set (0.00…

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

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

February 26, 2013 By 1 Comment

…FULLTEXT KEY `full_name` (`full_name`,`details`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 We also have identical tables created…add column FTS_DOC_ID | +———+——+————————————————–+ This doesn’t make a lot of sense to me. Why does InnoDB need to add a hidden column…

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

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… table rebuild by adding a new column and see what execution time is like when the default method is used: mysql> SET profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD COLUMN… index creation provides even better performance as compared to the default method, because it does not have to do random disk…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Comment: Implications of Metadata Locking Changes in MySQL 5.5

March 20, 2013 By 9 Comments

… t1 add column title2 varchar(30) default ‘ook’; (hang) mysql> commit; Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add column title2 varchar(30) default ‘ook… COMMIT /* xid=18 */ use `bruce`; alter table t1 add column title3 varchar(30) default ‘oook’ As you see,alter table is logged after…

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

…=InnoDB DEFAULT CHARSET=latin1 Created new table test._t_new OK. Altering new table… ALTER TABLE `test`.`_t_new` add column (foo char…`), KEY `guest_language` (`guest_language`,`guest_country`,`score`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Created new table test._t_new OK. This… –alter statement: Altering new table… ALTER TABLE `test`.`_t_new` add column (foo char(2)) Altered `test`.`_t_new` OK. If the…

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

Post: Hidden columns of query_review_history table

August 28, 2012 By Leave a Comment

…, you can manually add and have the following useful columns populated in query_review_history table: * `user_max` varchar(64) DEFAULT NULL – User who executed the query. * `host_max` varchar(64) DEFAULT…

Filed Under: Insight for DBAs, MySQL Tagged With:
« Older Posts