Mysql query larger than tmpfs (ramdisk)
Grrr.  Neither of the answers so far quite says the right things, so I feel obligated to give yet another Answer.

@jkavalik gives a nice description of why the problem occurred.  (But not what to do next.)

@DaveRix starts out good by saying "use RAM for caches, not RAM disk".  I very much agree.  But then he goes on to advocate dangerously high values for max_heap_size and tmp_table_size.  Those settings limit the amount of RAM allocated multiple time per connection.  That is, a complex query might allocate N*64MB.  This risks running out of RAM.  I suggest no more than 1% of RAM for them.

Another common mistake is to put tmpdir on a small disk filesystem.  This also causes abrupt halts.

Once tmpdir has room, @Cosi's query should run.  But perhaps not efficiently.  The next thing to do is look at the query, its EXPLAIN SELECT ..., the schema of the table(s), and especially the indexes.  Some common mistakes:


Subqueries instead of JOINs.
Single-column indexes when "composite" indexes would be better.