I have the following query:

select 
    t.Chunk as LeftChunk,
    t.ChunkHash as LeftChunkHash,
    q.Chunk as RightChunk,
    q.ChunkHash as RightChunkHash,
    count(t.ChunkHash) as ChunkCount
from
    chunksubset as t
    join
    chunksubset as q
    on
        t.ID = q.ID
group by LeftChunkHash, RightChunkHash

And the following explain table:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  subsets ref PRIMARY,IDIndex,SubsetIndex SubsetIndex 767 const   522014  "Using where; Using temporary; Using filesort"
1   SIMPLE  subsets eq_ref  PRIMARY,IDIndex,SubsetIndex PRIMARY 771 sotero.subsets.Id,const 1   "Using where; Using index"
1   SIMPLE  c   ref IDIndex IDIndex 4   sotero.subsets.Id   12  "Using where"
1   SIMPLE  c   ref IDIndex IDIndex 4   sotero.subsets.Id   12  

note the "using temporary; using filesort".

When this query is run, I quickly run out of RAM (presumably b/c of the temp table), and then the HDD kicks in, and the query slows to a halt.

I thought it might be an index issue, so I started adding a few that sort of made sense:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
chunks  0   PRIMARY 1   ChunkId A   17796190    NULL    NULL        BTREE       
chunks  1   ChunkHashIndex  1   ChunkHash   A   243783  NULL    NULL        BTREE       
chunks  1   IDIndex 1   Id  A   1483015 NULL    NULL        BTREE       
chunks  1   ChunkIndex  1   Chunk   A   243783  NULL    NULL        BTREE       
chunks  1   ChunkTypeIndex  1   ChunkType   A   2   NULL    NULL        BTREE       
chunks  1   chunkHashByChunkIDIndex 1   ChunkHash   A   243783  NULL    NULL        BTREE       
chunks  1   chunkHashByChunkIDIndex 2   ChunkId A   17796190    NULL    NULL        BTREE       
chunks  1   chunkHashByChunkTypeIndex   1   ChunkHash   A   243783  NULL    NULL        BTREE       
chunks  1   chunkHashByChunkTypeIndex   2   ChunkType   A   261708  NULL    NULL        BTREE       
chunks  1   chunkHashByIDIndex  1   ChunkHash   A   243783  NULL    NULL        BTREE       
chunks  1   chunkHashByIDIndex  2   Id  A   17796190    NULL    NULL        BTREE       

But still using the temporary table.

The db engine is MyISAM.

How can I get rid of the using temporary; using filesort in this query?

Just changing to InnoDB w/o explaining the underlying cause is not a particularly satisfying answer.

Besides, if the solution is to just add the proper index, then that's much easier than migrating to another db engine.