…,’%Y%m’) as sale_when, sum(quantity) as total_items, sum(price * quantity) as total_price, count(*) as total_lines from orders o…_expr(@mvid,’SUM‘,’quantity’,'total_items’); CALL flexviews.add_expr(@mvid,’SUM‘,’price * quantity’,'total_price’); CALL flexviews.add_expr(@mvid,’COUNT‘,’*',’total… than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Post: Color code your performance numbers
June 2, 2010 By 4 Comments
… that will be considered unacceptable: mysql> select sum(wtime=1.0 and wtime=3.)/count(*)*100 red_pct from performance_log_100601… our good requests. This eliminates slow outliers which we just count by their number and allows to see how good our…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024… similar name exists: SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024… data stored. SELECT engine, count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024*1024*1024…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(val) from ex1; +———–+ | sum(val) | +———–+ | 871537665… | +—–+———-+ 9 rows in set (0.00 sec) mysql> select sum(`count(*)`) from ex2; +—————–+ | sum(`count(*)`) | +—————–+ | 73027220 | +—————–+ 1 row in set (0.00 sec) This…
Comment: Quick comparison of MyISAM, Infobright, and MonetDB
October 1, 2009 By 30 Comments
… Infobright, I get mysql> SELECT sum(c19), sum(c89), sum(c129), count(*) FROM t WHERE c11< 5; +----------+----------+-----------+----------+ | sum(c19) | sum(c89) | sum(c129) | count(*) | +———-+———-+———–+———-+ | 2417861 | 2341752 | 2357072 | 487… (0.16 sec) mysql> SELECT sum(c19), sum(c89), sum(c129), count(*) FROM t WHERE c11> 5; +————+————+————+———-+ | sum(c19) | sum(c89) | sum(c129) | count(*) | +————+————+————+———-+ | 4995339851 | 4990774999 | 4998401490 | 999382…
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
…> select count(*) cnt, avg(wtime) avw, sum(if(wtime>0.3,1,0))/count(*) soso, sum(if(wtime>1,1,0))/count(*) poor, sum(if(wtime>5,1,0))/count(*) fatal…> select count(*) cnt, avg(wtime) avw, sum(if(wtime>0.3,1,0))/count(*) soso, sum(if(wtime>1,1,0))/count(*) poor, sum(if(wtime…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… in a moment. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_39323566` GROUP BY 1,2 ORDER BY… combined with this. — AGGREGATION SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_27656998` GROUP BY 1,2 ORDER BY…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
…: SET @query_id := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id ), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT(*) AS…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… – almost half of the time is spent counting the rows which are already traversed and counted for group by operation. The obvious optimization is to get rid of LIMIT 10 and just fetch all groups and sum values on the application side. Which is the second way. mysql> select grp, count(*) cnt from dt…
Post: How fast can MySQL Process Data
April 9, 2008 By 3 Comments
… count(*) from m3; +———-+ | count(*) | +———-+ | 10476840 | +———-+ 1 row in set (0.00 sec) mysql> select count(*) from m3 where i>0; +———-+ | count(*) | …I show only one here) mysql> select sum(i) from m3; +———+ | sum(i) | +———+ | 3492290 | +———+ 1 row in set…
