June 19, 2013

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

May 31, 2013 By Leave a Comment

…AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY `col2` (`col2`), KEY…

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

Comment: Derived Tables and Views Performance

May 21, 2013 By 16 Comments

…mysql auto_increment, `label` varchar(50) NOT NULL default ”, `link` varchar…

Filed Under: Insight for Developers Tagged With:

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

…primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it can deal with primary key…cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. … UUID such as VARCHAR(36) instead of auto-increment A: If …

Filed Under: Insight for DBAs, MySQL

Post: Long PRIMARY KEY for Innodb tables

October 3, 2006 By 8 Comments

… disk were stored in the database: CREATE TABLE `imagecache` ( `url` varchar(255) character set latin1 NOT NULL default ”, `thumb_width` smallint… in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not I use auto_increment id in…

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

… nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization,…varchar(250), `nr_order` int(11) DEFAULT NULL, `role_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `role_id_note` (`role_id`,`note`) ) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT…

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: , ,

Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By 30 Comments

… a small benchmark which shows MySQL performance when you use 3 different approaches: ENUM, VARCHAR and tinyint (+joined table) columns. … VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255) NOT NULL, PRIMARY KEY (id), KEY …

Filed Under: Benchmarks Tagged With: , , , , , , ,

Post: Adventures in archiving

February 11, 2013 By 16 Comments

…options for further testing. InnoDB’s inbuilt row compression MySQL‘s inbuilt compress() function leveraging zlib to provide …AUTO_INCREMENT, `dat` blob, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `compressed_row` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dat` varchar…

Filed Under: MySQL Tagged With: , ,

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

… NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(username… the PRIMARY KEY is always going to be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc…

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

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

… varchar(100) DEFAULT NULL, PRIMARY KEY (`flight_id`), KEY `UniqueCarrier` (`UniqueCarrier`,`AirlineID`,`Carrier`), KEY `AirlineID` (`AirlineID`,`Carrier`), KEY `…: 539279360 Data_free: 4194304 Auto_increment: NULL Create_time: 2011…socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

… int(10) unsigned NOT NULL, `full_name` varchar(100) DEFAULT NULL, `details` text, PRIMARY KEY (`id`), FULLTEXT KEY `full_name` (`full_name`,`details`) ) …ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000): …

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