…: 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…(@sum, 15), pow(@sum, 16), pow(@sum, 17), pow(@sum, 18); This query runs 500 iterations in 12s. So it looks like the optimizer was computing the SUM…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum values on the application… find it quite inconvenient whenever it is MySQL limitation or SQL Standard. This would be extremely useful feature and I do… implemented inside MySQL. Why am I looking on reporting performance optimization ? It is for ClickAider project which is growing rapidly and…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES(`origin_airport_id`), `count(*)`=`count(*)` + VALUES(`count(*)`), `sum(AirTime)`=`sum(AirTime)` + VALUES(`sum(AirTime)`), `sum…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
….sql create table dashboard_customer_sales AS select customer_id, customer_name, DATE_FORMAT(order_date,’%Y%m’) as sale_when, sum…_id(‘demo’,'dashboard_top_customers’), -> ‘select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer…, and the refresh will be more expensive. This is an optimization which is required to efficiently refresh MV which use those…
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… tried doing was loading the data with SQL statements. I wanted to see how fast …sum(c19), sum(c89), sum(c129) from t; select sum(c19), sum(c89), sum(c129) from t where c11 > 5; select sum(c19), sum(c89), sum…It’s a complete server with a different optimizer, etc. This point was hammered home during …
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
… World sample database and cooked up this query: select sql_no_cache sum(ci.Population) from City as ci where CountryCode in… the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population) from City as ci where speaks_english… to compare apples and uhm, apples… please comment). The poorly-optimized-subquery portion of the query essentially happens inside that function…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… BY 1,2,3 ORDER BY NULL ) The other important optimization combines results from multiple queries together. This query is single… be much more important in a moment. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_39323566` GROUP… the six branches are combined with this. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_27656998` GROUP…
