…use for cases when you do not need GROUP BY results sorted – it can significantly improve performance in some …to keep temporary table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c | …
Post: Wrong GROUP BY makes your queries fragile
September 6, 2006 By 7 Comments
… it, especially as it is connected to MySQL Performance. No it might not affect MySQL Performance per say but it limits our… of GROUP BY and may well correspond to different values. Which one are you looking to get ? In fact this is where results… and such GROUP BY statements may be faster than alternatives. Assume for example we have non-normalized table (or join result) where two…
Post: Should MySQL Extend GROUP BY Syntax ?
August 17, 2007 By 11 Comments
… value in the group: This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet… the groups and in fact ANSI SQL even forbids you to select columns which are not aggregates or part of group by because result… soring by population you would see MAX(Population) in the population column: SELECT City, Population City FROM City GROUP BY Country GROUPORDER BY Population…
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 Comments
MySQL has useful extention 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. Where…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… sum(cnt) from (select count(*) cnt from performance_log_080306 group by page having cnt>2) pv; Unfortunately this query ran for… exact result but approximate figure so I could trick MySQL to do group by a hash of the page instead of page itself: mysql> select sum(cnt) from (select count(*) cnt from performance_log_080306 group by…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…PRIMARY’ WHERE K.REFERENCED_TABLE_NAME IS NOT NULL GROUP BY K.CONSTRAINT_NAME; …result set, it shows us which rows in Bar are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…. 2-4 will be evaluated as math expression and the result will be IN(-2) which is not what you’re…: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort for group by by this column. This works because by…
Post: Testing MySQL column stores
August 16, 2010 By 15 Comments
… set against two MySQL column-store storage engines. I’d like to note that this effort was sponsored by Infobright, but this… of the queries. ICE supports almost all of the MySQL aggregation functions. Notably, GROUP_CONCAT is not supported, which is something I… was higher than it should have been, and several GROUP BY queries returned unexpected results as well. I did not change any “out…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
…plans for a query by changing indexes. Discarding the result is possible with pager: mysql> pager cat > /dev/…be done with: mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL DESC; +————-+——-+ | COMMAND | TOTAL | …
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result… get such results, and this is not any exception. Even spending a lot of time optimizing and otherwise working with MySQL I continue to run in results which surprise me. Some are later expected…
