…Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance…in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the…each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… with TokuDB, of course I wanted to compare InnoDB performance vs TokuDB. I have a particular workload I’m interested in…) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I…, $ID/1000, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; COMMIT; That is, our PRIMARY KEY is not sequential anymore, which is…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL …comparison on a single-node (on the left) vs. two-node (on the right) configuration. No surprises…nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… it gets from the index – typically (unless it is covering index) MySQL gets a “row pointer” which can be primary key value (for Innodb… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ | 1 | SIMPLE | idxtest | index_merge | i1,i2,combined | i1,i2 | 4…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… vs…+——————-+——————+——-+——+——+———+—————–+ | `ssb`.`lineorder` | GEN_CLUST_INDEX | 6909 | 2559 | 4350 | …key(P_Name), key(P_MFGR), key(P_Category), key(P_Brand) ); DROP TABLE IF EXISTS supplier; CREATE TABLE supplier ( S_SuppKey int primary key…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… or Web applications – medium size rows, auto increment primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int… with different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary key and full table scans. To…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…’,'Minnesota’,'Mississippi’,'Missouri’) NOT NULL, city varchar(255) NOT NULL, PRIMARY KEY (id), KEY state (state) ) ENGINE=MyISAM; 2) Table with VARCHAR: CREATE… quite expected – This is MyISAM table which is accessed via index, which means to retrieve each row MySQL will have to…: 1 select_type: SIMPLE table: s type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: test.c.state_id rows: 1 Extra…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied… for filtering records. For example, suppose you have a key defined as: KEY `i_l_partkey` (`l_partkey`,`l_quantity`,`l_shipmode…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `lineitem_fk2` (`l_suppkey`), KEY `lineitem_fk3` (`l_partkey`,`l_suppkey`), KEY `li_shp_dt_idx` (`l_shipdate`), KEY…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
… tested only READ queries, similar to ones in benchmark InnoDB vs MyISAM vs Falcon (http://www.mysqlperformanceblog.com/2007/01/08/innodb… or Web applications – medium size rows, auto increment primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id` int…
