GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Why temporary tables?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Why temporary tables?

    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.

    Comment


    • #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.

      Comment


      • #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.

        Comment


        • #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.

          Comment


          • #6
            Adding an index on fld4 will help.

            Comment

            Working...
            X