Bug #1981 Unneccessary use of filesort and temporary table when using GROUP BY
Submitted: 28 Nov 2003 14:42 Modified: 11 Dec 2003 10:31
Reporter: James Harvard Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.16 OS:MacOS (Mac OS X)
Assigned to: CPU Architecture:Any

[28 Nov 2003 14:42] James Harvard
Description:
I have a select that joins 4 tables, the last of which contains many related rows that I want to 
count.  Although the select only returns one row, the GROUP BY clause causes MySQL to use a 
filesort and temporary table (revealed by doing "EXPLAIN SELECT ...").

If I remove the GROUP BY clause the query executes faster, despite returning >100 rows instead of 
just one.

How to repeat:
-- the following select shows 'sing temporary; Using filesort' for 'companies'
EXPLAIN
SELECT
  c.co_name, r.roomname, s.saletime, count(l.sale) as lot_count
FROM
  companies c
LEFT JOIN
  salerooms r ON c.company = r.company AND r.roomname_search = 'oxford'
LEFT JOIN
  sales s ON r.saleroom = s.saleroom AND s.saledate = '2003-08-27'
LEFT JOIN
  lots l ON s.sale = l.sale
WHERE
  c.co_name_search = 'mallams'
GROUP BY
  l.sale
;

-- the following select appears well optimised and faster, despite selecting more rows

EXPLAIN
SELECT
  c.co_name, r.roomname, s.saletime, l.sale as lot_count
FROM
  companies c
LEFT JOIN
  salerooms r ON c.company = r.company AND r.roomname_search = 'oxford'
LEFT JOIN
  sales s ON r.saleroom = s.saleroom AND s.saledate = '2003-08-27'
LEFT JOIN
  lots l ON s.sale = l.sale
WHERE
  c.co_name_search = 'mallams'
;
[28 Nov 2003 14:53] James Harvard
I've uploaded a file with table structure & sample data for running the 'explain select ...' statements 
provided under 'how to repeat'.
[11 Dec 2003 10:31] MySQL Verification Team
If there is no index on l.sale, then temporary table has to be used.

Filesort can be avoided by adding ORDER BY NULL, in which case results will be unsorted. 

Also, EXPLAIN at this point does not know number of rows to be returned, so 
filesort is actually never done.