Question on Copying to tmp table

------_=_NextPart_001_01C67946.03FDE8A2
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi,

=20

I need information on Copying to tmp table status.

=20

Explain for some of our query shows 'Using temporary, Using Filesort'.
When query goes into this state it takes very long time.

=20

Need to understand the state using temporary and using filesort.

=20

For e.g. the query;

Select userid, sum(bandwidth) as bw from mfs group by userid order by bw
DESC limit 10;

=20

When the query says copying to tmp table, what it is copying? And where
it is copying?

Is it copying the computed data (in this case sum(bandwidth) to tmp
table?=20

=20

What is the use of the variable max_tmp_tables?

In my case it is set to 2. What happens if I execute 3 queries at the
same time which requires tmp table?

=20

I have set the tmp_table_size =3D 32MB. Based on what condition does
mysqld determines that it has to store the temporary data in memory or
disk?

Does it determine before executing the query? Or first it will copy the
temporary table to memory and determining it is not enough and then
copies to disk?

=20

Also there is a restriction of 4GB for table size, when it copies the
data to tmp table, what will happen if the tmp table size exceeds 4GB
limit?

=20

Ila.

=20

=20

=20

=20

=20

=20


------_=_NextPart_001_01C67946.03FDE8A2--