… in customers’ application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state…) Table with VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255) NOT… quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have to…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… and j columns to varchar while sticking to utf8 character set which we had as default ? Joining on Char columns completes in… fact this was expected as MyISAM uses key compression for varchar columns so random key lookups become significantly slower. I tried… bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you…
Post: Beware of MyISAM Key Cache mutex contention
August 12, 2008 By 10 Comments
… seemed. In reality inserting in parallel into different tables when indexes fit in memory results in very bad contention causing hundreds… option. This is of course because some of indexes on the tables were on CHAR/VARCHAR columns. Honestly I did not expect so… standard multi value inserts which is pretty good number for indexing tables, especially considering application was doing some updates along the…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… | pct_buffer_pool | +——————-+——————+——-+——+——+———+—————–+ | `ssb`.`lineorder` | GEN_CLUST_INDEX | 6001 | 2095 | 3906 | 964974 | 73.2544 | | …char(25), S_Address varchar(25), S_City char(10), S_Nation char(15), S_Region char(12), S_Phone char…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… `airport_code` char(3) DEFAULT NULL, `CityName` varchar(100) DEFAULT NULL, `State` char(2) DEFAULT NULL, `StateFips` varchar(10) DEFAULT NULL, `StateName` varchar(50) …row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL Create_…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_shipinstruct` char(25) DEFAULT NULL, `l_shipmode` char(10) DEFAULT NULL, `l_comment` varchar(… | 1 | SIMPLE | supplier | index | PRIMARY | i_s_nationkey | 5 | NULL | 20174 | 100.00 | Using index | | 1 | SIMPLE …
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…varchar(10) DEFAULT NULL, `Origin` char(5) DEFAULT NULL, `OriginCityName` varchar(100) DEFAULT NULL, `OriginState` char(2) DEFAULT NULL, `OriginStateFips` varchar(10) DEFAULT NULL, `OriginStateName` varchar…the curtain, they also do not have indexes like InfoBright, but results are impressive. On …
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… storage engine: CREATE TABLE `t1` ( `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`char_id`) ) ENGINE=InnoDB DEFAULT CHARSET… NOT NULL auto_increment, `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`id`), KEY (`char_id`) ) ENGINE=InnoDB DEFAULT…: ref possible_keys: char_id key: char_id key_len: 18 ref: test.t1.char_id rows: 1 Extra: Using index 2 rows in…
Post: How number of columns affects performance ?
September 28, 2009 By 12 Comments
…t1c99` ( `t1` tinyint(3) unsigned NOT NULL, `c99` char(99) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=… NULL, … `t99` tinyint(3) unsigned NOT NULL, `v1` varchar(1) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=… data is stored in BTREE index very similarly to how indexes are stored. Summary: Beware of …
Post: Testing InnoDB "Barracuda" format with compression
April 23, 2008 By 3 Comments
…:00:00′, `subject` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `url` varchar(255) NOT NULL, `num…NULL, `source` mediumint(8) unsigned NOT NULL, `hash` char(32) NOT NULL, `mod_is` tinyint(3) … in conclusion – Fast Index creation allows to speedup load 2 times (even indexes fit into memory) – …
