If you want to rig your mesh with an Armature, and want the ‘mirrored’ side to be individually controllable, e.g. the arms of a character, you have to apply the mirror modifier to create the actual vertices for the mirrored side.
Otherwise, without applying the mirror modifer, if you create a one bone armature and bind the mesh to it, the bone will deform both arms at once, an interesting effect, for example for a bird’s wing.
I don’t know where ‘apply’ would be used for the other modifier types.
Generally, you probably won’t apply a subsurf modifier, but sometimes you might want to. For example, I have a character where I have copied a section of his head to use as a hair particle emitting mesh. And when you copy a section of a curved mesh with subsurf, it sometimes doesn’t retain the same curve as the whole mesh would, so I copied the mesh, applied the subsurf modifier, and the deleted everything but the scalp. I needed more verts than the original scalp had anyway, in order to get better gradations in the hair length, so the applied modifier worked very well for that.
Applying an armature modifier can give you a mesh model in a posed position, which I can imagine some use for, although I haven’t done that myself.
Short answer, though, is generally no, you don’t usually apply most modifiers.
When modeling cloth, its often much simpler to crate a subdivided plane, add a softbody modifier, let it drape the object its on, then apply it at the frame you like.
When modeling things like electric cords or flex pipe, its much easier to model it as a straight cylinder,subdivide, add a curve modifier, shape it how you want it, then apply.
When modeling characters or objects to be exported, its always a good idea to apply your subsurf modifier before exporting.
Just a few of the many times you may want to use the apply button