…MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_sort_keys=on’ (only on MariaDB 5.5) optimizer… The query used is: select c_custkey, c_name, sum(l_extendedprice * (1 – l_… optimizer, as it is clearly a part of the optimizer‘s job to select…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… optimizations. Batched Key Access Traditionally, MySQL always uses Nested Loop Join to join two or more tables. What this means is that, select… made on MySQL 5.6 config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer_switch=’mrr_cost_based=off’ optimizer_switch=’batched… more about these variables here. The query used is: select l_orderkey, sum(l_extendedprice * (1 – l_discount)) as revenue, o_orderdate…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one… post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…mysql> select sum(total_lines) from dashboard_customer_sales ; +——————+ | sum(total_lines) | +——————+ | 155186550 | +——————+ 1 row in set (0.64 sec) mysql> select sum…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
How smart is the MySQL optimizer? If it sees an expression repeated many times, does it …: select sql_no_cache pow(@sum := (select sum(rental_id) from sakila.rental), 1), pow(@sum, 2), pow(@sum, 3), pow(@sum, 4), pow(@sum, 5), pow(@sum, 6), pow(@sum, 7), pow(@sum…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
… with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect… is queries that execute slower in new MySQL 6.0 version. Query is pretty simple SELECT sum(l_extendedprice * l_discount) as revenue… 6.0? Here is execution plain for 6.0: +—-+————-+———-+——-+—————+—————+———+——+———+———————————————–+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…,60,70,80,90,100); +————-+ | sum(events) | +————-+ | 3289092 | +————-+ 1 row in set (1.04 sec) mysql> explain select sum(events) from info where d… have something to learn about MySQL Optimizer gotchas 
At the same time I figured out how to make MySQL Optimizer to do what we…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…SELECT `origin_airport_id`, SUM(`count(*)`) AS `count(*)`, SUM(`sum(AirTime)`) AS `sum(AirTime)`, SUM(`sum(DepDelay)`) AS `sum(DepDelay)`, SUM(`sum(DepDelay >= 0) flight_delayed`) AS `sum…
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… = c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population) from City… to compare apples and uhm, apples… please comment). The poorly-optimized-subquery portion of the query essentially happens inside that function…
