Note that I'm an atheist, and so have only an outsiders perspective on this.
I feel there are two important but separate elements at play, one social and the other theological. The social aspect is more well-defined. While prayer can be private, it is very often a public action. Families pray together, people pray in churches/mosques/synagogues/temples, people announce that they're praying ("I'll pray for you"/"You're in my prayers"). In these instances, prayer acts as a way to gain recognition of your situation/problems/beliefs.
An example in Judaism (in which I have the most experience), every service has the Mourner's Kaddish towards the end, where those grieving stand and offer a prayer for their loved ones that passed. In doing so, the rest of the congregation is made aware of their grief and knows to comfort them. It's actually an ingenious and unobtrusive way to accomplish that (much better than going around telling everyone you just lost a relative).
The theological aspect is a little trickier, but I never bought the "God doesn't care" idea. Think of it this way: most monotheistic religions believe in a god that is:
- Omnipotent
- Omniscient
- Benevolent
If you truly believe all of these, then it really doesn't matter how insignificant the topic is. God will have a part in it. Furthermore, if it is benevolent, it won't want any harm to those who are good. Where prayer always strikes me as a little misplaced is with the omniscience. If God does see everything, then there's no need to call it attention to any of your problems. It already knows you have them.