… order by together with ROLLUP. This does not make much sense to me and I find it quite inconvenient whenever it is MySQL… faster. So GROUP BY WITH Rollup is not the killer solution for this problem, while it well could be if well implemented inside MySQL… as group by execution method, not temporary table as ordinary GROUP BY: mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by grp…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 12 Comments
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… stay away from such group by statements, however as usually there are exceptions and such GROUP BY statements may be faster than alternatives. Assume for… log GROUP BY id,login May be replaced by SELECT id,login,max(login_time) FROM log GROUP BY id which may be faster especially if GROUP BY…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
… or switch to filesort method and get result faster. I however picked another road which is quite …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… not exact value but normally close enough to make a decision…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…can be many orders of magnitude faster than the original query execution time… of the view, making it available for querying: mysql> call flexviews.enable( -> …_lines as ol USING (order_id) -> GROUP BY (customer_id) -> ORDER BY total_price desc -> LIMIT 10; +————-+————-+————-+ …
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
… important. Sphinx mysql> select max(forum_id) as m,author_id as a from sptest group by author_id order by m desc limit….47 sec) MySQL mysql> select max(forum_id) as m,author_id as a from sptest group by author_id order by m desc limit… is easy to “break” MySQL making it to do group by with on disk temporary table which will make Sphinx much faster and few others. The…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… between query runs) which does not make sense as there no data …table. Results: mysql> select count(*) cnt,c from gtest group by c order by null limit 10; +—–+——————————————+ | cnt | c…10-100 times faster than disk based MyISAM tables Hopefully MySQL will implement …
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… arithmetic to make the math faster, adding 10 + 10. This is a simple fact, that when you distribute computation you work faster. How… table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1 group by val; Query OK, 9….val = 0 group by 1,2 ; +—–+—–+———-+ | val | val | count(*) | +—–+—–+———-+ | 0 | 0 | 1 | +—–+—–+———-+ 1 row in set (0.00 sec) mysql> insert into ex2…
Post: Power of MySQL Storage Engines
March 5, 2007 By 3 Comments
… winning. When Falcon looses to Innodb in certain cases it makes Jim to try harder, if Heikki sees Innodb does not… and group by operations much faster than MySQL can and it can do it in parallel, which means it can be 100 times faster in the end, but there is no way to hook it up to standard order by and group by functions so…
Comment: Watch out for Marketing benchmarks
August 24, 2006 By 7 Comments
… MySQL 4 -> 5 upgrade there are variants. 1) If you application was not well optimized for MySQL 4 it may work much faster on MySQL 5 – for example if you used OR…) You may get benefit from MySQL optimizations – like index merge may improve performance, some changes making group by more efficient, improved join optimizer and…
Post: Using Sphinx as MySQL data retrieval accelerator
October 30, 2006 By 25 Comments
…date, group etc) and get sorted result set and claiming it is way faster than getting it with MySQL. Honestly …, plus there would be an option to retrieve by filters only with no full text query. Sphinx … with other databases which may be slower than MySQL. MySQL Make it so one would not need to use …
