…; So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB, and in it we have random…)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is bad for InnoDB, but this is what I need… = 16M max_heap_table_size = 64M sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 1000 query_cache_size…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… standalone Innodb instance Let’s break it down a bit. Our unit of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single Innodb node…_groups table to define the relationship between them. When someone joins a group, you run a transaction that adds the relationship…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…=’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…comment` varchar(101) DEFAULT NULL, PRIMARY KEY (`s_suppkey`), KEY `i_s_nationkey` (`s_nationkey`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… primary keys very carefully, especially with InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming… capacity. What does this have to do with InnoDB? Data clustering. InnoDB‘s primary keys define the physical order rows are stored… data warehousing workload. The nested-loop joins are not all that fast on big joins; the query optimizer can sometimes pick…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For each pk_column value in step…=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column value fetched from… lookups as well. But this works only with joins and specifically with Block Access Join Algorithms. So I am not going to…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…(255) NOT NULL, PRIMARY KEY (id), KEY state (state) ) ENGINE=MyISAM; 3) Table with INT: CREATE TABLE cities_join ( id int(10…(255) NOT NULL, PRIMARY KEY (id), KEY state_id (state_id) ) ENGINE=MyISAM; 4) Dictionary table for cities_join: CREATE TABLE IF… be larger. It is also interesting to note performance of Innodb tables in this case: for VARCHAR it takes about 0…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
…) bp JOIN innodb_sys_indexes ON id = index_id JOIN innodb_sys_tables ON table_id = innodb_sys_tables.id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables.name AND innodb… | a | PRIMARY | 59 | 0 | 0 | 0.08 | | sbtest | sbtest#P#p1 | PRIMARY | 22 | 0 | 0 | 22.68 | | sbtest | sbtest#P#p0 | PRIMARY | 22…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… InnoDB storage engine: CREATE TABLE `t1` ( `char_id` char(6) NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`char_id`) ) ENGINE=InnoDB… SELECT SQL_NO_CACHE COUNT(t1.char_id) > FROM t1 > JOIN t2 USING (char_id)\G *************************** 1. row *************************** id: 1 select…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… circumstances Innodb is actually faster than MyISAM in 2 cases out of 3. I guess the reasons are the following: Innodb primary key joins are very fast as data is clustered together with index and generally highly optimized Innodb builds hash indexes…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo…_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No…
