Q1: GROUP BY is the command that requests the SQL engine to "aggregate"
sets of rows based on values in common between those rows. Those columns
participating in the query that are NOT part of the aggregation key (the
key is composed of the columns specified in the GROUP BY clause) must have
some sort of function applied to them: COUNT(), SUM(), AVG(), MAX(), MIN(),
etc. in order for the aggregation to make sense.
DISTINCT is a keyword that indicates you desire only unique values. It's
exact effect will depend on what kind of clause it is modifying.
Q2: I do not believe that the GROUP BY *always* uses a temporary file. I
believe that if your memory is large enough and your engine is tuned
appropriately then more of your GROUP BY calculations will be performed in
memory rather than on disk.
Q3: The LIMIT clause of a select statement is applied to the results of the
GROUP BY aggregation and not to the data being aggregated. In fact, I would
guess that the difference in the two times would be only that amount of
time required to transmit the records beyond what the LIMIT clause allowed
from the server to your client.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
|---------+---------------------------->
| | "Lorderon" |
| | <[EMAIL PROTECTED]|
| | net.il> |
| | |
| | 07/06/2004 12:21 |
| | AM |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------|
|
|
| To: [EMAIL PROTECTED]
|
| cc:
|
| Fax to:
|
| Subject: GROUP BY vs DISTINCT - questions
|
>--------------------------------------------------------------------------------------------------------------------------------|
Hi,
1. What is the difference between GROUP BY and DISTINCT in the background
engine? How MySQL treats each one of them?
2. Why GROUP BY statement ALWAYS uses a temporary file???
3. Why using LIMIT with a GROUP BY statement takes about the same time as
without using LIMIT?
any answer would be appriciated...
-thanks, Lorderon.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
