I'm a ZEO user who suffers from Fibromyalgia & spinal problems. I used to have a top-of-the-line mattress, and was considering swapping it for a memory foam mattress; since I woke up every 30-45 minutes due to discomfort originating from pressure points where my body touches the mattress.
I've been sleeping on the memory foam mattress for several months now, and it has been a vast improvement. I still sleep poorly at night, but my waking periods aren't due to the pressure of where my body touches the mattress. The new mattress is "warmer" than the old one; so you have to reduce your blankets or increase your air circulation. My wife, however, likes the extra warmth...
This mattress is a bit unusual. It has "zippered" compartments for the various types of foam that it is constructed from. This allows top layer of memory foam (4") to be replaced with stiffer or softer versions, or replaced due to deterioration (age). Same with the lower sections. The manufacturer will help you fine tune your mattress by swapping out the top layer until you find the firmness/softness you want. The manufacturer also sells a breathable, stretchable, waterproof mattress cover so you won't have to worry about sweat/body fluids causing problems with the mattress.
So overall, I've found that the memory foam mattress is a worthwhile way to improve your sleep, especially if you suffer from things like Fibro.