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?
Setting your tmp_table_size is the correct way. Minimum value is 1024, I don't think setting it to 0 will work.
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;