Monstrous query hanging on "copying to tmp table" in MySQL
Wow, that is a doozie of a query. At this point it has little to do with your configuration variables and you should focus on how to optimize the query. 

It is tough to give you a magic bullet, though, without knowing what question you are trying to determine from this monstrous query. At first glance, it appears it is trying to do too much.

I would start by removing these two problems from your EXPLAIN plan:

1       PRIMARY &lt;derived2&gt;      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort
2       DERIVED &lt;derived3&gt;      ALL     NULL    NULL    NULL    NULL    397520  Using temporary; Using filesort


The first I think is the query for 'order_stats' derived table, the second I am assuming is the 'main' derived table, though it might be flipped. Regardless, I would start at the 'order_stats' (leave a comment with the pastebin of the explain and running time of non-explain, please).

Depending on what this query is trying to accomplish, I would recommend storing the results in a temporary table to be used in the join. One question I would like to know from your developer is, what is the english of the package_tickets and order_tickets computed columns. And if it could be accomplished another way if it turns out to be the bottleneck.