June 19, 2013

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

April 4, 2011 By 20 Comments

…rows affected (41 min 52.04 sec) Data dictionary Here is a quick example of the list of tables…same as the one specified above. Both views took about 40 minutes to build Deleted 100 order lines. Refreshed both views. Compared…

Filed Under: Insight for DBAs, MySQL

Post: A case for MariaDB's Hash Joins

May 31, 2012 By Leave a Comment

…row scanned performing a lookup in the hash table by using values of the columns participating in the equijoin condition. The hash table…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…Handler_read_key and Innodb_rows_read to be incremented by TWO. It does not …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: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…compare query results between MyISAM FTS and InnoDB FTS over the same…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…of the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index_table…

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

…compared…two rows): mysql> insert into test.demo values (NULL,2),(NULL,3); Query OK, 2 rows…rows in the changelog, each representing one of the changes we made. You will notice that the source table only has two columns…

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

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…two extents same as primary key tree or stored in the same extent ? HT: An InnoDB extent is a contiguous block of…of threads allowed inside InnoDB. Value…columns form a consecutive set of leftmost columns of…

Filed Under: Insight for Developers Tagged With: ,

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 amount of rows full table scan performs about 25 times better than accessing rows…

Filed Under: Benchmarks Tagged With: , , , , , , ,

Post: Dynamic row format for MEMORY tables

September 6, 2011 By 5 Comments

…tables can have BLOB and TEXT columns, and VARCHAR columns will not waste space due to implicit extension to CHAR. The lack of…same for both row formats. (Speaking very strictly, the VM Peak value is not comparable due to slightly different number of rows… The results of this test (two client threads…

Filed Under: MySQL, Percona Software
« Older Posts