…query with aggregation, a JOIN, and a WHERE clause that uses an IN clause: — INPUT SQL: select origin_airport_id, count(*), sum(AirTime), sum…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… later. SQL features used in the query Incremental refresh doesn’t support all of the SQL features…top_customers’), -> ‘select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> … will be more expensive. This is an optimization which is required to efficiently refresh MV…
Post: Optimizing repeated subexpressions in MySQL
January 23, 2009 By 11 Comments
…: 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, 14), pow(@sum, 15), pow(@sum, 16), pow(@sum, 17), pow(@sum, 18); This query runs 500 iterations in 12s. So it looks like the optimizer…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…some silly like query to illustrate some search is required, so count(*) can’t be optimized away … store result in temporary table and run sum() and sort query on that table instead if amount …quite inconvenient whenever it is MySQL limitation or SQL Standard. This would be extremely useful feature …
Post: Quick comparison of MyISAM, Infobright, and MonetDB
September 29, 2009 By 30 Comments
… doing was loading the data with SQL statements. I wanted to see how fast…queries: select 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…’s a complete server with a different optimizer, etc. This point was hammered home …
Post: A micro-benchmark of stored routines in MySQL
August 16, 2009 By 7 Comments
…query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population) from City as ci where speaks_english(CountryCode) > 0; +——————–+ | sum… and uhm, apples… please comment). The poorly-optimized-subquery portion of the query essentially happens inside that function now. And …
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…3 ORDER BY NULL ) The other important optimization combines results from multiple queries together. This query is single threaded, and thus this serves…the six branches are combined with this. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_27656998` …
