… the third blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is targeted at the join related optimizations introduced in… will be created based on the columns of table t1 that are two be used to join rows with table t2. This step…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5…. be depend on the number of rows that match the condition ‘key_column=x’. You can see that … counters Handler_read_key and Innodb_rows_read to be incremented by TWO. It does not actually mean…
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: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… improve query response times compared to accessing the non-…in one transaction (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…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
…in MySQL in 2008): The key columns must come before any non-key variable-length columns. For performance reasons, the key columns are stored in…comparable due to slightly different number of rows inserted). In the second test we check if the dynamic row…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… connections as in MySQL. This is…Rows_sent: 1 Rows_examined: 655360 use test; SET timestamp=1325145746; select count(*) from auto_inc; Compare…/84%), wp_blog_two (36/11%)… 1 more # …column in the above output. The query execution time is 4ms, it sends 202 rows for which it has to examine 1012 rows…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…mysql> show table status like ‘ontime_fact’G *************************** 1. row *************************** Name: ontime_fact Engine: InnoDB Version: 10 Row_format: Compact Rows: 6697533 Avg_row…compared to Innodb, 8:1 compared…In general, a column store performs about 8x-10x better than a row…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
…rows: mysql> select ltrim_junk_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql> select remove_dups_mysql…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…In cases where this is not useful, simply ignore this column…both views. Compared the …mysql> select sum(total_lines) from complete_example2 ; +——————+ | sum(total_lines) | +——————+ | 155186550 | +——————+ 1 row in set (0.68 sec) Conclusion Flexviews supports two…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
…. There are however some interesting exception. Compare those two queries for example. If you look only … second which uses direct column value needs to do the filesort: mysql> explain select * from tst …_len: 5 ref: const rows: 10 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * …
