MySQL Bugs: #2933: Large temp when using a "DISTINCT *" and an "ORDER BY" clause

Bug #2933	Large temp when using a "DISTINCT *" and an "ORDER BY" clause
Submitted:	24 Feb 2004 4:27	Modified:	25 Mar 2004 10:56
Reporter:	Ludovic Desmet	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	mysql-4.0.18	OS:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When using a distinct on a larger column list than the order by clause generate enormous temp file.

The query 
SELECT DISTINCT table.*
FROM table
WHERE table.value > 0
ORDER table.name.

generates several gigabytes for less than one megabyte.

I have also tried the same query with some modifications:
(1) removing the distinct: it is ok
(2) removing the order by: is is ok 
(3) Only "distinct table.name" instead of "distinct *": it is ok
(4) Indexing table.name: not ok
(5) Indexing table.value: not ok

How to repeat:
(1) Create a table with several columns (Text, Integer and Timestamp is sufficient) without any primary key.
(2) Insert few thousands rows.
(3) Execute: 

SELECT DISTINCT table.*
FROM table
WHERE table.value > 0
ORDER table.name.

How many rows are involved, and how many TEXT/VARCHAR/BLOB columns are present in the table?  MySQL will use max_sort_length bytes for each such column (per row) needed to process the query, so this can result in temporary files being significantly larger than one might expect.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".