Why temporary tables?

MySQL & MariaDB Other MySQL® Questions
1

Hello.
I have following table schema:
CREATE TABLE tbl (
fld1 varchar(32) character set utf8 NOT NULL default ‘’,
fld2 bigint(20) default NULL,
fld3 mediumtext character set utf8,
fld4 tinyint(4) NOT NULL default ‘0’,
fld5 varchar(32) character set utf8 default NULL,
fld6 text character set utf8,
fld7 varchar(32) character set utf8 default NULL,
fld8 bigint(20) NOT NULL default ‘0’,
fld9 varchar(32) character set utf8 default NULL,
fld10 varchar(32) NOT NULL default ‘’,
PRIMARY KEY (fld1),
UNIQUE KEY fld5 (fld5),
KEY fld10 (fld10)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
If I do a explain of query:
select distinct fld10 from tbl where fld4=2;
it says me:
“1” “SIMPLE” “tbl” “ALL” \N \N \N \N “13” “Using where; Using temporary”

The query returns me an empty record set. Because there are currently no records with fld=4.
Now my question is: why does MySQL need to create a temporary table?
In addition MYSQL creates the tmp table on disk, which is
slow.
tmp_table_size and max_heap…is 512M

Hope somebody can help me…
Regards

2

What MySQL version do you use? I wasn’t able to reproduce it on 5.0.51 on Debian Lenny.

3

Hello,
I use mysql 5.0.26 on a centos linux server. I use the binaries.
I already thought about updating mysql’s version but currently
we cannot stop the production server,
therefore I’m searching the reason about this phenomenon.
Greez.

4

Is it because you have a field with mediumtext data type?

I have that question myself. I know queries with TEXT/BLOB columns will NOT use temporary tables but go to disk directly. But it looks like it is applicable for the other ‘types’ of BLOB and TEXT (TINYTEXT/MEDIUMTEXT/LONGTEXT and TINYBLOB/MEDIUMBLOB/LONGBLOB) data types well.

5

The temporary table appears because of DISTINCT clause.
MySQL needs temporary table to store all possible values of fld10.
It cannot use index for this purpose.

6

Adding an index on fld4 will help.