MySQL query with 2 joins, large keylen leads to 'Copying to tmp table on disk' process hanging forever

As said by the EXPLAIN, you key size is HUGE : 602, this requires MySQL to write down the data.

You need to reduce (greatly) the keylen, I believe recommended is below 128.

I suggest you create a column called MD5_FrenchWord which will contain the MD5 value of FrenchWord. Then use this column for the GROUP BY. This assumes that you are looking for similarities, when you group by rather than the actual value

You are misusing GROUP BY. This clause is entirely pointless unless you also have a summary function such as MAX(something) or COUNT(*) in your SELECT clause.

Try removing GROUP BY and see if it helps.

It's not clear what you're trying to do with GROUP BY. But you might try SELECT DISTINCT if you're trying to dedup your result set.

Looking further at this question, it seems like you might benefit from a couple of compound indexes.

First, can you make sure your table declarations have NOT NULL in as many columns as possible?

Second, you're retrieving Pronunciation, FrenchWord, and id from your Frenchwords table, so try this compound index on that table. Your query will then be able to get what it needs directly from the index, saving a bunch of disk io. Notice that Pronunciation is mentioned first in the compound index declaration because that's the value you're searching for. This allows MySQL to do a lookup on the index, and get the other information it needs directly from the index, without thrashing back to the table itself.

(Pronunciation, FrenchWord, id)

You're retrieving Englishword from Englishwords looking it up by id. So, the same reasoning can apply to this compound index.

(id, Englishword)

Finally, I can't tell what your ORDER BY is for, once you use SELECT DISTINCT. You might try getting rid of it. But it probably makes no difference.

Give this a try. If your MySQL server is still thrashing after you make these changes, you have some kind of configuration problem.