Hookahs are used for smoking shisha out of, it's a pleasant thing to do. Also, they work well for weed. My buddy's got one, and smokes out of it on special occassions... has three hoses so three people can take a rip at once. It's a pain in the ass to set up, though, so he doesn't bother with it on most days. Smoke is smooth and cool, couldn't ask for more. But his steamroller/bubbler is more efficient and gets you more fucked up for the amount of weed you throw in...
Hookah is a nice novelty, though, Won't deny it.