… that I used: ## InnoDB options innodb_buffer_pool_size = 55G innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb…, PRIMARY KEY (`transactionid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… size. In MySQL 5.6 the buffer size used by MRR can be controlled by the variable read_rnd_buffer_size, while MariaDB… = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT…. Handler_mrr_rowid_refills counts how many times the buffer used by MRR had to be reinitialized, because the buffer was small…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… briefly explain these optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What…’ and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In… because these have an impact on MRR performance, and BKA uses the MRR interface, so these buffers indirectly impact BKA performance…
Comment: Dynamic row format for MEMORY tables
April 5, 2012 By 3 Comments
Hi Laurynas Biveinis. You mean current percona server use dynamic format memory table even though it is internal temporary table. So, Would below query use dynamic format internal temporary table. ? (of course tab table doesn… have index on fd2 columns) SELECT fd2, count(*) FROM tab GROUP BY fd2; Best regards, Lee.
Comment: Some fun with R visualization
February 28, 2012 By 5 Comments
Will, That is also not so hard, though some time is needed to figure it out. you can use ddply and summarize, something like that: d1
Post: Announcement of Percona XtraDB Cluster 5.5.20 GA release
April 3, 2012 By 10 Comments
… is developed by Codership Oy company and Galera 2.x supports such new features as: Incremental State Transfer, especially useful for WAN….com/downloads/Percona-XtraDB-Cluster/ Documentation Codership Wiki General Discussion group Launchpad project Bug reports Previous posts on this topic: Percona…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… amount of groups is much larger). The other idea came to my mind is using GROUP BY WITH ROLLUP so I can get grouped result set together with total value for the groups: mysql> select grp… I forced FileSort execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same time as GROUP BY WITH ROLLUP.
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 43 Comments
MySQL has useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group…
Post: Should MySQL Extend GROUP BY Syntax ?
August 17, 2007 By 11 Comments
… some value in the group: This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific… to get one row from the group which is sorted some way you have to use ugly hacks. This is because SQL… soring by population you would see MAX(Population) in the population column: SELECT City, Population City FROM City GROUP BY Country GROUPORDER BY Population…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… sum(cnt) from (select count(*) cnt from performance_log_080306 group by page having cnt>2) pv; Unfortunately this query ran for… sum(cnt) from (select count(*) cnt from performance_log_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt) | +———-+ | 1127031 | +———-+ 1… – quite handy. Another trick I want to share which I use a lot when I want to analyze data distribution but…
