…tests setup and clarifies the scope of descriptors optimization. Server Configuration: [mysqld] user=root port=3306 …innodb_log_file_size = 2000M innodb_log_files_in_group = 2 innodb_file_per_table = true innodb_…-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order-ranges=0 –oltp-…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… counted for group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum values on… number of groups which you can fetch on the application side (you can store result in temporary table and run sum() and sort query on that table instead if amount of groups is much larger). The other…
Post: Goal driven performance optimization
December 22, 2008 By Leave a Comment
…focus only on Server Side optimization – the Client Side Optimization is also quite …formula applies wall_time=cpu_time+sum(wait_time)+lost_time The …page_type=’search’ and wtime>1 group by h; +——+———-+—————–+——————+ | h | count(*) | avg(wtime) | sphinx_ratio | …
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… me top slowest sessions: mk-query-digest –group-by=Thread_id –order-by=Query_time:sum in > out Spot on, the session… mk-query-digest would do a nested group-by and order-by within a group so I would avoid the extra step, but then even better than that would be if it would optimize the…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
…optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer…query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as…09′ and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate,…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…SUM(`sum(AirTime)`) AS `sum(AirTime)`, SUM(`sum(DepDelay)`) AS `sum(DepDelay)`, SUM(`sum(DepDelay >= 0) flight_delayed`) AS `sum(DepDelay >= 0) flight_delayed` FROM `aggregation_tmp_74082863` GROUP…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… range with IN list in this query: mysql> explain select sum(events) from info where d IN(’2007-01-01′,’2007…’,’2007-01-31′) and group_id in (10,20,30,40,50,60,70,80,90,100); +————-+ | sum(events) | +————-+ | 3289092 | +————-+ 1 row… why it does not work for multiple group remains question to check with MySQL Optimizer team 
UPDATE: I just heard back from…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb_buffer_page_lru where table_name like ‘%ssb%’ group by…(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb_buffer_page_lru where table_name like ‘%ssb%’ group by….000000 | 0.001000 | 0 | 0 | 0 | 0 | 3 | 3 | | 8 | optimizing | 0.000048 | 0.000048 | 0.000000 | 0.000000 | 0 | 0…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… flexviews.add_expr(@mvid,’GROUP‘,’DATE_FORMAT(order_date,”%Y%m”)’,'sale_when’); CALL flexviews.add_expr(@mvid,’SUM‘,’quantity’,'total_items…’), -> ‘select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer_sales dsc ‘> group by customer_id…, and the refresh will be more expensive. This is an optimization which is required to efficiently refresh MV which use those…
Comment: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… table (with incremental materialization optimizations) we’ll call #tmp –to node1: select c1 as expr1, sum(c2) as expr, sum(1) as expr3… = t1.c1 group by 1; –to node2 (note the query is identical): select c1 as expr1, sum(c2) as expr2, sum(1) as… query are sent to user: select expr1, sum(expr2) expr2, sum(expr3) expr3 from #tmp group by 1; –Clean up drop table #tmp…
