June 18, 2013

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

May 31, 2013 By Leave a Comment

…mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL…

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

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

…NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set…tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to…

Filed Under: Insight for DBAs, MySQL

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

…, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to complete. If you’…Table: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…ALTER TABLE test add column c char(32) default ‘dummy_text’ | | 3 | msandbox | localhost | test | Query | 0 | NULL | show processlist | +—-+———-+———–+——+———+——+———————————+————————————————————-+ 3 rows in set…

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

Post: Converting Character Sets

March 17, 2009 By 22 Comments

…: mysql> CREATE TABLE `t1` ( ->   `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET…

Filed Under: Insight for Developers Tagged With: , ,

Post: Should you name indexes while doing ALTER TABLE ?

May 28, 2008 By 10 Comments

MySQL Server does not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what might…(11) default NULL, `j` int(11) default NULL, KEY `i` (`i`,`j`), KEY `i_2` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0…

Filed Under: Insight for DBAs Tagged With:

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…to the amazing MySQL manual, especially the option and variable reference table. But just as frequently, I want to…

Filed Under: Insight for DBAs, MySQL

Post: A recovery trivia or how to recover from a lost ibdata1 file

June 3, 2011 By 8 Comments

…tables mysql> show create table test.tablesG *************************** 1. row *************************** Table: tables Create Table: CREATE TABLE `tables` ( `name` varchar(100) DEFAULT NULL, `spaceid` int(11) NOT NULL, PRIMARY KEY (`spaceid`) ) ENGINE=MyISAM DEFAULT…

Filed Under: MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…default method is used: mysql> SET profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE…not applicable: UNIQUE indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

February 26, 2013 By 1 Comment

…TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar(100) DEFAULT NULL…ALTER TABLE statement, but it’s not possible to CREATE more than one at a time. If you try it, this is what happens: mysql> alter table…

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