… can see I’m using ORDER BY NULL clause as otherwise MySQL will sort the data after performing group by which is overhead we’re not looking to measure. This is actually good trick to use for cases when you do not need GROUP BY results sorted – it can significantly improve…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… PEOPLE GROUP BY GENDER This query will return only couple of rows but it is clear all rows from the table were used… WHERE COUNTRY=’USA’ GROUP BY GENDER even though full table scan is performed only rows with COUNTRY=’USA’ are used in results set… complicated, for example: SELECT * FROM (SELECT COUNTRY,COUNT(*) FROM PEOPLE GROUP BY COUNTRY) C WHERE COUNTRY=’USA’ As of MySQL 5.0…
Comment: GROUP_CONCAT useful GROUP BY extension
September 7, 2006 By 49 Comments
[...] GROUP_CONCAT useful GROUP BY extension: MySQL has useful extension to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns a string result with the concatenated non-NULL values from a group. [...]
Post: Using flow control functions for performance monitoring queries
May 24, 2008 By 12 Comments
…(*) fatal,page_type from performance_log_080523 where bot=’google’ group by page_type; +——-+——————-+——–+——–+——–+—————+ | cnt | avw | soso | poor | fatal | page_type | +——-+——————-+——–+——–+——–+—————+ | 21954… important to do such grouping by user functions because otherwise you will not catch important but may be less used functions performance problems…
Post: How to generate per-database traffic statistics using mk-query-digest
September 16, 2009 By Leave a Comment
… mk-query-digest, and that is the –group-by argument. This can be used to aggregate information by tables, hosts, users, or databases (full documentation is available via perldoc). %> perl mk-query-digest –limit 100% –group-by…
Comment: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 24, 2007 By 14 Comments
… in any direction by columns you use for group by but if you’re using group by with roll up you can’t sort by value of aggregate…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…, however it can also be used just for sort for example if you’re just using ORDER BY without and where clauses on…) things like ORDER BY A ; ORDER BY A,B ; ORDER BY A DESC, B DESC will be able to use full index for sorting… “using index” in EXPLAIN output. These are the main “core” use for indexes. You can also see others like using index for group by but…
Comment: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
October 10, 2007 By 14 Comments
[...] Using GROUP BY WITH ROLLUP for Reporting Performance Optimization | MySQL Performance Blog Using GROUP BY WITH ROLLUP for Reporting Performance Optimization (tags: development performance mysql) [...]
Comment: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
….1.41.7788 You can rewrite non-distributable aggregate functions using GROUP BY rewriting: select a expr1, count(distinct b) expr2, count(*) expr3…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… JOIN dim_date using (date_id) join dim_flight using(flight_id) WHERE DepDelay>10 AND Year=2007 GROUP BY Carrier ORDER BY c DESC… DepDelay>10 GROUP BY Year) t JOIN (select Year, count(*) as c2 from ontime_fact join dim_date using (date_id) GROUP BY Year) t2… and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve when grouping and filtering is used. Again, notice…
