June 18, 2013

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

May 31, 2013 By Leave a Comment

…to be exact): mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE…int(11) default null # `col3` varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE…

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

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

February 26, 2013 By 1 Comment

…VARCHAR(255) “title” and “details” with a TEXT “body”. CREATE TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar…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: , , , , ,

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

…to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar…mysql> alter table parent rename parentx; Query OK, 0 rows affected (0.05 sec) mysql> drop table…

Filed Under: Insight for DBAs, MySQL

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

…mysql: mysql> use test Database changed mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql…mysql> alter table test_concat add data varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table…

Filed Under: MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…mysql> ALTER TABLE t MODIFY v VARCHAR(8); Query OK, 4194304 rows affected (1 min 13.69 sec) Records: 4194304 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE…table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…in a VARCHAR(36)?  When tables …change the lifecycle of your application in advance. What about partitioning in MySQL… by ALTER TABLE — a typical reason to …int unsigned not null, clicks int unsigned not null, impressions int unsigned not null, blue_clicks int unsigned not null, blue_impressions int…

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

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

…index actually used. To some extent, MySQL 5.0 and later changed this situation …INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0, user_type TINYINT UNSIGNED NOT NULL DEFAULT 0, username VARCHAR…’s no need to go mucking about with server configuration, altering the table structure, …

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

Post: PROCEDURE ANALYSE

March 23, 2009 By 9 Comments

…varchar(128) | NO | MUL | | | | uid | int(11) | NO | MUL | 0 | | | status | int(11) | NO | MUL | 1 | | | created | int(11) | NO | MUL | 0 | | | changed | int…to do an ALTER TABLE…

Filed Under: Insight for DBAs Tagged With: