The good foam is called High Resiliance Foam. You can get it from foam stores that custom cut foam for reupholstery or custom upholstery. If you get it in 4" or less layers you can even roll it up or fold it if you need to transport it (although the layers do move independently and can be a bit of a pain, although two layers lets you get a harder bottom layer and softer top layer). It is expensive but less than the really expensive mattresses (e.g. 8" of king size via 2 twin XL size would be under $900 from where I purchased mine a couple of years ago, I'm not sure if they could do king or queen size as a single piece).

As someone who has trouble sleeping I've found what I sleep on seems to make surprisingly little difference as long as it isn't causing pain. Also, the breathable thing is likely a difference between cold sleepers and warm sleepers; as a cold sleeper I use a waterproofing layer and prefer that to a breathable mattress.

Your mileage may vary. I have a memory foam mattress and it works fine for me. I never noticed a particular sweating problem, haven't noticed any problems turning over. But, then, I just try to pick a position and go to sleep. I don't look for a comfortable position, per se, more of "I've conditioned myself to go to sleep when my body goes to this position".

I like modal sheets which run very high in absorption, so perhaps covering with extremely breathable bed materials makes a difference.

>Don't buy memory foam, it doesn't breathe well, makes you sweat and if its a thick layer of memory foam will make it impossible to turn on at night (you will sink into a stupid, sealed hole)

I bought a high-quality memory foam mattress from a major brand and don't have these issues. There is some "tech" associated with mattress construction to counter these potential problems. One of the best purchases I've made.