… TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY…: # `col2` int(11) default null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
… TABLE `menu` ( `id` int(11) NOT NULL auto_increment, `label` varchar(50) NOT NULL default ”, `link` varchar(100) NOT NULL default ‘#’, `parent` int(11…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…; 2) Table with VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255) NOT NULL… ( id int(10) unsigned NOT NULL auto_increment, state_id tinyint(3) unsigned NOT NULL, city varchar(255) NOT NULL, PRIMARY KEY (id…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
… VARCHAR(1000) fields per client requirements. First off the schema tables. CREATE TABLE `archive_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dat` varchar… 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…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… on. Here and in later examples I’m extending the VARCHAR column to trigger table rebuilds without affecting the table size… Max_data_length: 0 Index_length: 278839296 Data_free: 1838153728 Auto_increment: 4587468 Create_time: 2011-11-06 10:01:18 Update… the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… more times. Q: In which cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You… impact on indexing to use wider UUID such as VARCHAR(36) instead of auto-increment A: If you’re using UUID it is… get table which is larger than if you would use auto increment. Having said that there are many people using UUID rather…
Comment: Why MySQL could be slow with large tables ?
May 27, 2009 By 141 Comments
…=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1 ROW_FORMAT=FIXED AUTO_INCREMENT=10100001 ; The country codes stored in different table named iplist…` int(11) unsigned NOT NULL AUTO_INCREMENT, `code` varchar(2) NOT NULL, `code_3` varchar(3) NOT NULL, `name` varchar(255) NOT NULL, `start` int…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… majority of the database was a single table with an auto-incrementing integer PK and a secondary UNIQUE KEY. The queries being…: CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED NOT NULL, last… – INT UNSIGNED. Do the math. The maximum value for an auto-increment INT UNSIGNED is 4294967295. Divide that by 1500 qps and…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… using auto_increment functionality This is right. With some > exception however. For example Innodb tables do internal full table > lock if auto_increment is… storing them as fixed-width data (think of converting your varchars to chars for example), then having to do byte comparisons…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
…` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp… actor ( -> actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, -> first_name VARCHAR(45) NOT NULL, -> last_name VARCHAR(45) NOT NULL, -> last_update TIMESTAMP…
