# Why temporary tables?

**URL:** <https://forums.percona.com/t/why-temporary-tables/812>\
**Category:** Other MySQL® Questions\
**Created:** [June 25, 2008, 3:21am UTC](https://forums.percona.com/t/why-temporary-tables/812 "2008-06-25T03:21:45Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ltsbozen](https://avatars.discourse-cdn.com/v4/letter/l/74df32/32.png) [@ltsbozen](https://forums.percona.com/u/ltsbozen)\
**Post date:** [June 25, 2008, 3:21am UTC](https://forums.percona.com/t/why-temporary-tables/812/1 "2008-06-25T03:21:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![debug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/debug/32/772_2.png) [@debug](https://forums.percona.com/u/debug)\
**Post date:** [June 30, 2008, 3:55am UTC](https://forums.percona.com/t/why-temporary-tables/812/2 "2008-06-30T03:55:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ltsbozen](https://avatars.discourse-cdn.com/v4/letter/l/74df32/32.png) [@ltsbozen](https://forums.percona.com/u/ltsbozen)\
**Post date:** [June 30, 2008, 4:06am UTC](https://forums.percona.com/t/why-temporary-tables/812/3 "2008-06-30T04:06:05Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![thiru](https://avatars.discourse-cdn.com/v4/letter/t/b9e5f3/32.png) [@thiru](https://forums.percona.com/u/thiru)\
**Post date:** [December 16, 2008, 5:10pm UTC](https://forums.percona.com/t/why-temporary-tables/812/4 "2008-12-16T17:10:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AlexN](https://avatars.discourse-cdn.com/v4/letter/a/b9bd4f/32.png) [@AlexN](https://forums.percona.com/u/AlexN)\
**Post date:** [December 17, 2008, 8:13am UTC](https://forums.percona.com/t/why-temporary-tables/812/5 "2008-12-17T08:13:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [March 21, 2009, 7:11pm UTC](https://forums.percona.com/t/why-temporary-tables/812/6 "2009-03-21T19:11:31Z")

</div>

Adding an index on fld4 will help.