… reporting you may be interested in best selling product or top sales people. This information may often need simple select query… BY does not work with it. The thing is – it is using filesort 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: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… dashboard_top_customers MV, ordering them in descending order: mysql> call flexviews.create( -> ‘demo’,'dashboard_top_customers’,'COMPLETE…top_customers’), -> ‘select customer_id, ‘> sum(total_price) total_price, ‘> sum(total_lines) total_lines ‘> from demo.dashboard_customer_sales dsc ‘> group by…
Post: How fast can you sort data with MySQL ?
August 18, 2007 By 26 Comments
… I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows… set (0.56 sec) Not bad ! Even though MySQL does not optimize “get top N sorted rows” very well it takes just…_buffer_size=10000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (1.34 sec) mysql> show status…
Post: Top 5 Wishes for MySQL
June 29, 2007 By 10 Comments
… have much support from MySQL to do so. What is really bad is Parallel query. Reporting with group by over 10.000.000… XA which were released in MySQL 5.0 without consistent work with MySQL close Partner – Innobase to ensure Group Commit still works in… years in MySQL I have created more than hundred of such little projects suggested by MySQL customers and myself based on long MySQL usage…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…BY A ; ORDER BY A,B ; ORDER BY A DESC, B DESC will be able to use full index for sorting (note MySQL… many pages requiring potentially a lot of IOs. On top of that if you just need access to couple …. You can also see others like using index for group by but I think they can be pretty much looked …
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…: 117 Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘dim1′\G *************************** 1. row … dim2.dim2_id and fact.col2 > some_const group by fact.col1 There are indexes on all … previous post, which I linked at the top of this post, we can scan the…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
… explain due to several layers of cascaded views built on top of one another so it is easy to write the… more modular and using MySQL VIEWs ? mysql> create view user_counts as select user_id,count(*) cnt from comments group by user_id; Query OK, 0 rows affected (0.00 sec) mysql> select * from user_counts where…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is …_type, count(*) from information_schema.innodb_buffer_pool_content group by 1, 2, 3 into outfile ‘/tmp/buffer-pool-… these three sets of stats to find the top tables on the active server and left-join …
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 16 Comments
… of records. This table is partitioned by month which means that MySQL can use partition pruning to reduce …:hidden;text-indent:0px;padding-left:3px;border-top:1px solid #CCC;border-right:1px solid #CCC;… ’2007-01-01′ and ’2007-12-31′ GROUP BY carrier ORDER BY 2 DESC; .tblGenFixed td {padding:0 3px;…
Post: Optimizing slow web pages with mk-query-digest
April 7, 2011 By 14 Comments
… that are consuming most MySQL time and that’s how mk-query-digest groups and orders data by default. Fixing top 10 queries on… list me top slowest queries, I asked it to list me top slowest sessions: mk-query-digest –group-by=Thread_id –order-by=Query_time… the top. And what do you know, 519 queries were run during that session which took 148s seconds overall: # ######################################################################## # Report grouped by Thread…
