June 20, 2013

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…two major factors: The cost (in terms of execution time) of…column `mview$pk` which is an auto_increment BIGINT surrogate key for the table…same as the one specified above. Both views took about 40 minutes to build Deleted 100 order lines. Refreshed both views. Compared…, is the value proposition for …

Filed Under: Insight for DBAs, MySQL

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…table by using values of the columns participating in the equijoin condition. The hash table is accessed by using a hash function on the values of…compared to doing batched index lookups (BKA). However, in Test C the cost of hash join remains the same but the cost of…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…of quirky behavior; here, we are going to run some queries and compare the result sets. Our hope is that the one of two…value of ‘test/innodb_myisam_stopword’ And here’s what appeared in the server’s error log: InnoDB: invalid column type for stopword table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…value of ‘pk_column‘ which is a part of the secondary key tuple, a point primary key lookup is made against base table, the number of…, when MRR is used then there are two separate calls made to the storage …value for counter Handler_read_key is more or less the same for MariaDB 5.5 when compared…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

…compare a search key K to an index record R if we only knew the suffix of R. There are, of…same benefit if you’re performing INSERT with multiple values? We build up INSERTS on our clients and insert values…columns form a consecutive set of leftmost columns of…

Filed Under: Insight for Developers Tagged With: ,

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

… also does not take into account locality of access, as you could be reading the same page multiple times. Consider if you… first a hash table will be created based on the columns of table t1 that are two be used to join rows with table t2. This… is slow as compared to MySQL 5.6. Next interesting thing are the last two columns of the table above and the values for ‘Handler_read…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…part will compare query results between MyISAM FTS and InnoDB FTS over the same data sets…value of an ID column that you defined for the underlying table, or it can be a sequence value generated by InnoDB when the table… additional data into the table, run an OPTIMIZE or two, delete some data, and see…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5… and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , ,

Post: Using Flexviews - part two, change data capture

March 25, 2011 By 26 Comments

…improve query response times compared to accessing the non…transaction (two rows): mysql> insert into test.demo values (NULL…of the changes we made. You will notice that the source table only has two columns…of the changes, respectively. Finally, note that the two insertions happened inside of the same…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By 30 Comments

…table) columns. In practice you can also often use 4th variant which is not comparable directly, which is using integer value and having value…same, but join query performance is 30% lower. Also note the times themselves – traversing about same amount of rows full table…

Filed Under: Benchmarks Tagged With: , , , , , , ,
« Older Posts