… of follow and order by follow。 I wrote: SELECT follow,count(*) AS NUM FROM wp_fans GROUP BY follow order by NUM desc limit 5 Follow was index。 the explain: type:index，Extra:Using index; Using temporary; Using filesort i am…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… 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…: Using where; Using temporary; Using filesort 1 row in set (0.01 sec) mysql> explain select grp, count(*) cnt from dt where slack like “a%” group by…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…: 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: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT… i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 100.00 Using where…
Post: Speeding up GROUP BY if you want aproximate results
March 7, 2008 By 24 Comments
…allocate more memory to the temporary table or switch to filesort method and get result faster…. (select count(*) cnt from performance_log_080306 group by crc32(page) having cnt>3) pv -> ; +———-+ | sum(cnt) | +———-+ | 1127031… Another trick I want to share which I use a lot when I want to …
Post: GROUP_CONCAT useful GROUP BY extension
September 4, 2006 By 49 Comments
…useful extention to the GROUP BY operation: function GROUP_CONCAT: GROUP_CONCAT(expr) – This function returns …with joins, but sometimes we need the temporary ids in clients code, for example … thing: you may want to add ORDER BY NULL statement after GROUP_BY to avoid unnecessary sorting with filesort…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… a query with both a GROUP BY and an ORDER BY clause? Right; you’re stuck, unless your query groups by the same column as… see “Using temporary; Using filesort” in the EXPLAIN report. Hopefully, the size of this interim result set is relatively small, after the GROUP BY reduces it…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | lineitem | ref | i_l_suppkey | i_l_suppkey | 5 | dbt3.supplier.s_suppkey | 292 | 100.00 | Using…> ’1996-05-01′ GROUP BY o_orderkey ORDER BY num_items DESC LIMIT 10; …
Comment: How much overhead is caused by on disk temporary tables
August 17, 2007 By 26 Comments
… has 2 ways of doing group by via filesort and with temporary table. filesort returns result in sorted order just by nature. I’m not sure why Monty decided to sort results by default even in case temporary table was used. May…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…dim2_id and fact.col2 > some_const group by fact.col1 There are indexes on… | fact | ALL | 147367284 | Using temporary; Using filesort | | dim1 | eq_ref | 1 | Using where | | dim2 | eq_ref | 1 | Using where | +——-+———–+———–+———————————+ As…
