… 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 these point primary key lookups will be depend on the number of rows that match the condition ‘key_column=x… in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5.6 code? As MRR was used in both MySQL…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… when the workload fits entirely in memory. For the purpose of benchmarking in-memory workload, the InnoDB … obvious improvement is shown by the high numbers of Innodb_buffer_pool_read_ahead when the …as compared to MySQL 5.6. Next interesting thing are the last two columns of the table above and …
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in… new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
…), table with 1 tinyint column and char(99) column and table with 100 tinyint columns. The former two tables have the same row length but have number of column… columns. But is it because of large number of columns or dynamic format on its own is slave ? I have tested yet another table structure: CREATE TABLE… than Innodb when it comes to in memory full table scan. P.S Tests were done on MySQL 5.4.2 on Intel…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… up the longest sum of run time, query ranked #1. The first row in the table above shows the Count of number of times this query was executed. Now let’s take a look at the values in the 95% column… underlying tables involved and the query execution plan used by MySQL. The end result might be that you end up limiting the number of…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… MyISAM tables with many columns (the same one Peter wrote about recently). The client’s performance is suffering in part because of the number of columns, which… and the like. The query timer is in milliseconds (why doesn’t mysql show query times in microseconds? I had to resort to…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… tables would keep only couple of small rows, but require at least 16K page in Innodb), keeping all tables open in Innodb dictionary and number of other challenges in IO… help because the column user_id is not part of the view so would not be available even if MySQL would support triggers…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… creates a ranking function automatically. In cases where this is not useful, simply ignore this column. It is used to prevent wide… this summary to calculate the number of lines in the order_lines table, three orders of magnitude more quickly than COUNT(*). mysql> select count(*) cnt from…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…column in the EXPLAIN output of the query was totally off. The actual number of rows was 18 times more than the number of rows reported by MySQL in…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
…in the community to remove this limitation. In 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL…columns are stored in a fixed-length block at the beginning of the row. If there were any variable-length column in… different number of rows inserted). In the …
