CODEWITHSUNDEEP
mysqlquery-optimizationtemp-tables
guigoz
guigoz

Reputation: 704

Can I force MySQL to use on-disk internal tables?

If I know in advance that the internal tables created by a query can't fit into the available memory, can I force MySQL to use on-disk internal tables, to avoid in-memory creations and then HEAP to DISK conversions? Is one way to temporarily

set tmp_table_size = 0

or is there a better way?

Upvotes: 1

Views: 101

Answers (2)

Rick James
Rick James

Reputation: 142433

This setting is made specifically your your case:

SET big_tables = ON

(And turn it off afterward.)

before the SELECT.

Caveats: Deprecated in MariaDB 10.5 in favor of SET tmp_memory_table_size = 0;

Upvotes: 1

Gordan Bobić
Gordan Bobić

Reputation: 1888

Setting your tmp_table_size is the correct way. Minimum value is 1024, I don't think setting it to 0 will work.

Upvotes: 1

Related Questions