Re: Outfile Using too much disk space

[Rick James]

And if it had been utf8, it would be 3*255 instead of 255.

Interesting. I would have expected it to be smarter in the sorting.

What version of MySQL?

Here is roughly what the steps are. (I think of 'buffer' as something in RAM, so I won't use that term.)

1. Table scan to find the rows of interest (WHERE...); put results into a temp MyISAM table.
2. Sort that temp table (ORDER BY...).
3. Write the output to the OUTFILE.

The 81GB is excessive, and it seems excessive to need 4 * 81GB.

SHOW VARIABLES LIKE 'character%'; -- wondering if it is switching to utf8.
SHOW VARIABLES LIKE '%table%';
EXPLAIN SELECT ...;

I guess your test case gives a strong indication that VARCHAR(255) is _not_ necessarily the same as VARCHAR(20).

If you need to do this task a lot, suggest you add
INDEX(customer_id, member_id)