… UNSIGNED DEFAULT ’0′ NOT NULL, c CHAR(120) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY …’0′, `id` bigint(20) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT ’0′, PRIMARY KEY…max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 …
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
… customers’ application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state… table for cities_join: CREATE TABLE IF NOT EXISTS `states` ( `id` tinyint(3) NOT NULL auto_increment, `name` char(40) NOT NULL…): 0.085637 3) Results for INT + join: select SQL_NO_CACHE c.city from cities_join c JOIN states s ON (s.id = c…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…switch=’join_cache_incremental=on’ optimizer_switch=’join_cache_hashed=on’ optimizer_switch=’join_cache_bka=on’ join_cache_level=4 join_buffer_size=32M join…` date DEFAULT NULL, `o_orderpriority` char(15) DEFAULT NULL, `o_clerk` char(15) DEFAULT NULL, `o_shippriority` int(11) DEFAULT NULL, `o_comment…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…_plugin_int*) (handler.cc:435) ==9090== by 0x7A212A: plugin_initialize(st_plugin_int*) (sql_plugin.cc:1048) ==9090== by 0x7A5166: plugin_init(int*, char**, int…_show.cc:5522) ==9090== by 0x74E4DF: get_schema_tables_result(JOIN*, enum_schema_table_state) (sql_show.cc:6238) ==9090== by…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… table and join query performed on INT fields: CREATE TABLE `intjoin` ( `i` int(10) unsigned NOT NULL, `c` char(10) DEFAULT NULL, `j` int(10… default ? Joining on Char columns completes in 4.5 seconds on Innodb which is about 50% slower compared to Joining on Int, for MyISAM… compared to integer based join. So how do I read these results ? CHAR keys are indeed slower for joins compared to integer keys…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
…, PRIMARY KEY (`char_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `t2` ( `id` int unsigned NOT NULL auto_increment, `char_id` char(6) NOT…(`test`.`t1`.`char_id`) AS `COUNT(t1.char_id)` from `test`.`t1` join `test`.`t2` where (`test`.`t1`.`char_id` = convert(`test`.`t2`.`char_id…(`test`.`t1`.`char_id`) AS `COUNT(t1.char_id)` from `test`.`t1` join `test`.`t2` where (`test`.`t2`.`char_id` = `test`.`t1`.`char_id`) 1…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… join clause to be id, i and C columns appropriately. CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL default ’0′, `i` int(10) unsigned NOT NULL default ’0′, `c` char(15) default NULL, `pad` char(8… MyISAM still a bit better by primary key join than for secondary key join. I guess because it knows for sure there…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… static int 14184 join_init_cache(THD *thd,JOIN_TAB *tables,uint table_count) 14185 { … snip … 14268 cache->length=length+blobs*sizeof(char*); 14269 cache->blobs=blobs; 14270 *blob_ptr=0; /* End sequentel */ 14271 size=max(thd->variables.join_buff_size…
Comment: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
August 4, 2009 By 30 Comments
In the test you used int+join table, what if we move the join table functionality to our application and just compare ENUM VS CHAR VS INT.
Post: Be careful when joining on CONCAT
October 16, 2007 By 16 Comments
… simple join. It was a join on tb1.vid = CONCAT(‘prefix-’, tb2.id) with tb1.vid – indexed varchar(100) and tb2.id – int(11) column. No matter what I did – forced it to use key, forced a different join order, it did… tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix-’, CAST(tb2.ID AS CHAR)) AND -> tb2.gid…
