Options to stop query from using all TMPDIR space?

[Bryan Welch]

This query fills up my 135GB tmpdir mount and makes MySQL quite unhappy. I'm looking for a way to export the same data without filling tmpdir, of course.

select 'question_instance_code','answer_code','freeform_answer','order_position ' union select distinct ai.question_instance_id as question_instance_code ,ai.answer_id as answer_code ,freeform_answer ,order_position into outfile '/reports/answers.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' from answer_instance ai where ai.domain_id = 1234567;

At first, it looked like creating a temporary table would avoid using tmpdir, but this query wasn't any better.

create table answers_csv as
select 'question_instance_code','answer_code','freeform_answer','order_position '
union select distinct ai.question_instance_id as question_instance_code ,ai.answer_id as answer_code ,freeform_answer ,order_position
from answer_instance ai where ai.domain_id = 1234567;

Could anyone recommend options that would minimize tmpdir space?

thanks,
Bryan