MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Arnaud Date:November 20 2003 3:38pm
Subject:temporary table always created on disk
View as plain text  
Hi all.

I alreaedy posted about this, but I get more and more confused !
My temporary tables are ALWAYS created as temporary disk tables !

Here is the query :
SELECT commerces.enseigne FROM cellules INNER JOIN commerces ON
(cellules.indexCelluleCommerce=commerces.indexCelluleCommerce) WHERE
cellules.CodeSite = "SIT7401011" GROUP BY cellules.indexcellulecommerce

the fields commerces.enseigne and cellules.CodeSite are CHAR(255),
cellules.indexcellulecommerce is INT(11)

Here is the explain :
EXPLAIN SELECT commerces.enseigne FROM cellules INNER JOIN commerces ON
(cellules.indexCelluleCommerce=commerces.indexCelluleCommerce)   WHERE
cellules.CodeSite = "SIT7401011" GROUP BY cellules.indexcellulecommerce

Result :
| cellules | ref | CodeSite,IndexCelluleCommerce | CodeSite | 21 | const |
34 | where used; Using temporary |
| commerces | ref | IndexCelluleCommerce | IndexCelluleCommerce | 5 |
cellules.IndexCelluleCommerce | 20 | where used |

All the fields in this query are indexed. (not unique)

Now my server variables :
tmp_table_size = 384M
max_heap_table_size = 384M
version = 3.23.54-nt

And the status (the annoying part!!!) :
created_tmp_disk_tables  1
created_disk_tables  1
(I checked before and after the query, they both get increased by one)

I understand that the temporary table is created because of the GROUP BY
clause, but why on disk ??? How could I work around this?

Thanks a lot if anyone can help!

Arnaud

Thread
temporary table always created on diskArnaud20 Nov