June 19, 2013

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

…table metadata lock | ALTER TABLE test add column c char(32) default ‘dummy_text’ | | 3 | msandbox | localhost | test | Query | 0 | NULL…1 | +—+ 1 row in set (0.00 sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1…

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

…TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar(100) DEFAULT NULL…mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0 rows affected, 1 warning (39.73 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql…

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

Post: Converting Character Sets

March 17, 2009 By 22 Comments

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

Filed Under: Insight for Developers Tagged With: , ,

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 t ADD COLUMN v VARCHAR(1); Query OK, 4194304 rows affected (1…table. There’s also a number of cases when this optimization is not applicable: UNIQUE indexes in ALTER TABLE…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

…mysql> alter table antest_innodb type=innodb; Query OK, 245760 rows affected, 1 warning (51.87 sec) Records: 245760 Duplicates: 0 Warnings: 0 mysql…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…mysql: SHOW CREATE TABLE information_schema.innodb_ft_default_stopword\G *************************** 1. row *************************** Table: INNODB_FT_DEFAULT_STOPWORD Create Table: CREATE TEMPORARY TABLE `INNODB_FT_DEFAULT_STOPWORD` ( `value` varchar(18) NOT NULL DEFAULT…

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

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

…table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table ADD…mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE…

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

Post: Joining on range? Wrong!

May 17, 2010 By 10 Comments

…TABLE `items_ordered` ( `itm_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `itm_prd_id` int(10) unsigned NOT NULL, `itm_order_timestamp` timestamp NOT NULL DEFAULT…ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD…

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

Post: COUNT(*) vs COUNT(col)

April 10, 2007 By 45 Comments

…TABLE `fact` ( `i` int(10) unsigned NOT NULL, `val` int(11) default NULL, `val2` int(10) unsigned NOT NULL, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql…

Filed Under: Insight for Developers Tagged With: ,

Post: Percona testing: Quick test clusters with kewpie!

January 13, 2012 By Leave a Comment

…mysql> create table t1 (a int not null auto_increment, primary key(a)); Query OK, 0 rows affected (0.11 sec) mysql…1 | |  4 | |  7 | | 10 | | 13 | +—-+ 5 rows in set (0.00 sec) mysql> exit Bye Should you wish to alter the number…

Filed Under: Benchmarks, MySQL