I have a query which takes a very long time to run but produces a new table in the end. The actual joins are not all that slow but it spends almost all of its time in 'copying to tmp table' and during this time the status of all other queries (which should be going to unrelated tables) is 'locked'. I am in the process of optimizing the long query but it is ok for it to take a while since it is an offline process, but it is NOT ok for it to stop all other queries which should not be related to it anyway. Does anyone know why all other unrelated queries would comeback as 'locked' and how to prevent this behavior?
You are right in that "unrelated tables" shouldn't be affected. They shouldn't and to my knowledge they aren't.
There is a lot of information over at MySQL regarding locks, storage engines and ways of dealing with it.
To limit locks I would suggest that you write an application that reads all data needed to do this new table and simply have your application insert values to the new table. This might take longer but it will do it in smaller chunks and have less or no locks.
Good luck!