…config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch…’ and l_shipdate > ’1995-03-09′ group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate LIMIT 10; In…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer_switch=’mrr_cost_based… = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
… and counted for group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum values… as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by… 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: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
…. Here are couple of examples: GROUP BY Consider SELECT COUNT(*) cnt, page FROM log GROUP BY page ORDER BY cnt DESC limit 10, the query… sure your most frequent queries do not need GROUP BY. It can be reachable by normalization or other techniques. COUNT Many people think…. ORDER BY I already wrote about ORDER BY LIMIT Optimization so I will not repeat it here. In the nutshell ORDER BY LIMIT can…
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 important… page_type=’search’ and wtime>1 group by h; +——+———-+—————–+——————+ | h | count(*) | avg(wtime) | sphinx_ratio | +——+———-+—————–+——————+ | 00…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… to list me top slowest sessions: mk-query-digest –group-by=Thread_id –order-by=Query_time:sum in > out Spot on, the… if 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…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… is about. Let’s start with the MySQL query optimizer. The optimizer tries to choose the best join order based on its… go to disk. (This distinction is abstracted away by the storage engine; the optimizer doesn’t know how a given storage engine….dim2_id = dim2.dim2_id and fact.col2 > some_const group by fact.col1 There are indexes on all the columns in…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…. Other common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC LIMIT 10 – If… set just because schema is not optimally indexed – this is easy. For example our ORDER BY rank query is such – adding index… doing so by several queries, assuming this query would not need to analyze much more rows because it is optimized differently. One…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…_date.Year IN (2008,2009) group by 1; –PARALLEL OPTIMIZATIONS: * Base level table name: `aggregation_tmp#74082863` * IN list optimization enabled * Detected an IN…_date.date_id) WHERE dim_date.Year IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… automatically !) there is potential for optimizing this query. For example: SELECT GENDER, COUNT(*) FROM PEOPLE GROUP BY GENDER This query will return only… and query SELECT GENDER, COUNT(*) FROM PEOPLE WHERE COUNTRY=’USA’ GROUP BY GENDER even though full table scan is performed only rows…
