We have been running into a problem where querying a table that has approximately 50 million rows, and has an index size of 4 GB (table size of about 6 GB) results in the database server swapping memory, and slowing down dramatically. I'm pretty sure this has to do with the temp table size being exceeded, and it being swapped to disk.
If I upgraded my database server from 32 GB of RAM to 64 GB RAM, I'm wondering if the MySQL database will be able to fully take advantage of this additional memory and not swap. I've gone through a few of the variables (e.g. KEY_BUFFER_SIZE, etc...), and they seem to support setting values over 64 GB. However, the MySQL documentation says that the tmp_table_size maxes out at 4 GB.
So would the memory upgrade be worth it? Would the "querying-large-table" problem benefit from this, or would it not help because of the 4 GB limit? I know there are potentially other solutions like restructuring the table to be partitioned out in different ways, etc... but without changing anything about the table, would additional memory help?
Also, in general, are there any other memory related variables that MySQL wouldn't be able to take advantage of when moving from 32 to 64 GB RAM?
We are using 64bit linux (Ubuntu) as our database server.