Mr.Carramba: We have thought of keeping the huge tables in RAM (temporarily) and writing them to disk when we are finished with them. It's definitely something we will try. Also, optimizing MySQL is indeed a very good idea, but what optimizations would you recommend? Any good resources on this topic?
moberry: memcached is only useful when doing lots of identical reading queries. My brother's program that will run on the server does pretty much the opposite, running always-different queries and mostly writing ones, so I don't think memcached would improve performance that much.

As for the partitioning scheme, I think I'm going to keep a separate /mysql partition (as long as there's no performance regression) just for the sake of having that partition intact in case of system failure (broken update, outside attack, overload etc.).

Thanks