…-only transactions, start suffering from the trx_list overhead created by concurrent updates. Once we step away from this spherical read… –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp-simple-ranges=0 –oltp-sum-ranges=0 –oltp-order…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… with the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film…(*) AS Calls, SUM(DURATION) / COUNT(*) AS “R/Call” FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… 1: Default config for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5.6 with all default… s_region = ‘AMERICA’ group by d_year, p_brand order by d_year, p_brand; — Q2.2 select sum(lo_revenue), d_year, p… s_region = ‘ASIA’ group by d_year, p_brand order by d_year, p_brand; — Q2.3 select sum(lo_revenue), d_year, p…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer_sales dsc ‘> group by customer_id ‘> order by total_price desc; ‘> ‘); Query OK, 1 row affected (0.00 sec) When the view is ‘enabled’, the contents are created: mysql…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law applies…BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count(*)`, SUM(`sum(AirTime)`) AS `sum(AirTime)`, SUM(`sum(DepDelay)`) AS `sum(DepDelay)`, SUM(`sum(DepDelay …
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… TOP 5 accessed tables via mysql> select OBJECT_NAME,COUNT_STAR from table_io_waits_summary_by_table order by COUNT_STAR DESC LIMIT… SUM_NUMBER_OF_BYTES_READ: 361611264 SUM_NUMBER_OF_BYTES_WRITE: 326303744 or we can get top tables that required read IO mysql> select * from file_summary_by_instance order by…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum(data_length)/(1024…),’G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES GROUP BY engine ORDER BY sum(data_length+index_length…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b…
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
… run by the native database interface (MySQL): mysql> select word, md5(word), md5(reverse(word)), count(*) from words2 group by 1,2,3 order by 3… SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_39323566` GROUP BY 1,2 ORDER BY 1 ASC ON DUPLICATE KEY… SQL: SELECT `word`,`md5(word)`,SUM(`count(*)`) AS `count(*)` FROM `aggregation_tmp_27656998` GROUP BY 1,2 ORDER BY 1 ASC ON DUPLICATE KEY…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… groups is relatively small: mysql> select grp, count(*) cnt from dt where slack like “a%” group by grp order by null; +——-+—–+ | grp | cnt | +——-+—–+ | 2257… side (you can store result in temporary table and run sum() and sort query on that table instead if amount of… an extra count. So if MySQL does not allow us to use ORDER BY together with GROUP BY WITH ROLLUP can we still do…
