query always creates a disk-based temporary table!

[cocobingo]

Hi!

I have a query that as to create a temporary table to execute. The problem is that this table is ALWAYS created on disk!

First of all (because that will be your first guess), here are my server-variables:

tmp_table_size=512M
max_heap_table_size=512M
version : 3.23.54-nt
OS : win2OOO

So the server should not create a disk-based tmp table if its size is not grater than that, right?

Now the query (simplified for clarity, but still producing the behaviour) :

SELECT cellules.indexcellulecommerce
FROM cellules INNER JOIN commerces
ON (cellules.indexcellulecommerce=commerces.indexcellulecommerce)
WHERE cellules.codesite="SIT7401001" AND commerces.codesite="SIT7401001"
GROUP BY cellules.indexcellulecommerce

...and the result of the 'explain' :

table;type;possible_keys;key;key_len;ref;rows;Extra
cellules;ref;CodeSite,IndexCelluleCommerce;CodeSite;21;const;751; "where used, Using temporary"
commerces;ref;CodeSite,IndexCelluleCommerce;IndexCelluleCommerce;5;cellules.IndexCelluleCommerce;20;"where used"

So as you can see, the number of rows scanned is at most 751*21 = ~15000 rows, and the only fields needed for this query are codesite (21 length) and indexcellulecommerce (5 length). So the query can definitely not exceed the tmp_table size.
Now the next point : there is no temporary table created (not even in memory) if I remove the GROUP BY clause! So why does this GROUP BY have to be executed on a disk based temporary table??? I have 2GB of RAM on my Win2k server, and it looks like mysql doesn't want to use them!

Thanks a lot for any suggestion!

Arnaud

 

[hvass]

If you notice whenever you do a group by it also does an order by

It may be the order by that insists on generating a temporary table - not necessarily a physical one but one in memory.

Have a look at

http://www.mysql.com/doc/en/ORDER_BY_optimisation.html

You could try it with ORDER BY NULL so that it wont bother doing the sort

- in fact I must remember that for myself I could probably improve some of my processing by removing the sort requirement from a group by

 

[cocobingo]

Thanks hvass.

I checked this page before asking, and indeed, whenever I add an ORDER BY clause on another field than the one I am grouping on, the explain has a "using file sort" comment for the first table.
But as you mentionned, the temporary table used for the group by does not have to be a physical one. Mine always is !!!

 

[hvass]

Scrabbling around here but are you using the default SET options

http://www.mysql.com/doc/en/SET_OPTION.html

BIG_TABLES=1 would make all temporary tables disk based

 

[cocobingo]

Well... I thik I have t obe a little more precise.
My mySQL server is in fact a distribution called LassoMySQL, that ships with Lasso (a web-oriented scripting language). The doc states that the LassoMySQL distribution is a MySQL version 3.23.54 (the show variables command agrees with that). It runs as a service under Win2000, so I have no control over the startup options. I can only change the mysql.ini file.
I tried to execute "SHOW SESSION VARIABLES" and "SHOW GLOBAL VARIABLES", but MySQL gave me a syntax error in both cases.
I tried "SET BIG_TABLES=0", but again the same syntax error.
I tried "SET OPTION SQL_BIG_TABLES=0" and it worked, but the result was "0 rows affected" !!!
I re-checked my query after issuing this command, and temporary tables are still created i-on disk (

Arnaud

 

[hvass]

>>I tried "SET OPTION SQL_BIG_TABLES=0" and it worked, but the result was "0 rows affected" !!!

Don't worry "0 rows affected" does not mean it did not work it means that it did not produce any records in the output data set.

SHOW SESSION VARIABLES is new in 4.0.3 try SHOW VARIABLES but

Otherwise cannot think of anything else to look at sorry

 

[cocobingo]

SHOW VARAIBLES does not output anything like BIG_TABLES or SQL_BIG_TABLES. I'll try to ask this in a Lasso forum and see if I get any answer.

Thanks for helping!

Arnaud