From: Michael Widenius
Date: April 18 1999 9:21pm
Subject: temporary tables
List-Archive: http://lists.mysql.com/mysql/2035
Message-Id: <14106.19228.349673.60186@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
>>>>> "Matthias" == Matthias Pigulla writes:
Matthias> Hi Monty, hi subscribers,
Matthias> I wonder wheter anyone could explain me (or point me to the manual
Matthias> section I haven't found by now) when temporary tables have to be created
Matthias> when SELECTing from a table.
Matthias> E.g., "SELECT text FROM table WHERE id < 10" won't need an internal temp
Matthias> table, but "SELECT 1+1" would, right?
No; Neither of the above needs a temporary table.
From the manual:
------
If there is an ORDER BY clause and a different GROUP BY clause, or if
the ORDER BY or GROUP BY contains columns from tables other than the
first table in the join queue, a temporary table is created.
--------
As DISTINCT is converted to a GROUP BY on all columns, DISTINCT +
ORDER BY will in many cases also need a temporary table.
If you use 'SQL_SMALL_RESULT' MySQL will use an in-memory temporary table.
Regards,
Monty