…_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), KEY `col2` (`col2`), KEY `col2_2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET… # test.test # ######################################################################## # Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…` int(11) NOT NULL auto_increment, `label` varchar(50) NOT NULL default ”, `link` varchar(100) NOT NULL default ‘#’, `parent` int(11) NOT NULL default ’0′, `sort` int(11) default NULL, PRIMARY KEY (`id`)) —————————————————————————————————— $mysql…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…(10) unsigned DEFAULT NULL, `CLUSTER_NAME` varchar(255) DEFAULT NULL, `SPACE` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`NAME`) ) ENGINE=InnoDB DEFAULT CHARSET…, `POS` int(10) unsigned NOT NULL, `COL_NAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`INDEX_ID`,`POS`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Fields…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… generating md5 digest over tuples in a table with no primary key was not deterministic which could lead to a node failure…-uniform foreign key reference could cause a slave crash. Fixed by using primary key of the child table when appending exclusive key for cascading… would fail in case mixed CHAR and VARCHAR columns would be used in foreign key definitions. Bug fixed #1100496 (Seppo Jaakola). Debian…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key… disk were stored in the database: CREATE TABLE `imagecache` ( `url` varchar(255) character set latin1 NOT NULL default ”, `thumb_width` smallint…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…’) NOT NULL, city varchar(255) NOT NULL, PRIMARY KEY (id), KEY state (state) ) ENGINE=MyISAM; 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, PRIMARY KEY (id), KEY…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… disable foreign keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `child` ( `parent_id` int(11) NOT NULL, `v` varchar…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables. If…. So what if we convert i and j columns to varchar while sticking to utf8 character set which we had as… as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=0 which typically…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… how integer primary key is handled. I can’t see the index size difference between DEFAULT and 1 values for such key. However… string keys but not integer keys is the biggest reason joins using string keys is so much slower compared to integer keys. Integer keys still…` varchar(20) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… primary key, C_Name varchar(25), C_Address varchar(25), C_City varchar(10), C_Nation varchar(15), C_Region varchar(12), C_Phone varchar(15), C_MktSegment varchar…_PartKey int primary key, P_Name varchar(25), P_MFGR varchar(10), P_Category varchar(10), P_Brand varchar(15), P_Colour varchar(15), P_Type varchar(25…
