i don't know if anyone has said any of this yet, but I'll say what I've got. I recently played a chieftain game as the Americans, and did some experimentation and came up with the following (this is probably gonna be long):
First, irrigation will give you ONE extra food on any irrigatible terrain square, except for a grassland (if it already has two food, it most likely will not go up to three, but it happens on occasion - usually next to a river). Mining will give you ONE extra shield, on any terrain square.
now for the specifics
(x/y = x is food, y is shield):
(M = mine, I = irrigate)
Grassland:
1/1 - M/I, will yield one extra of whichever
2/0 - M, will yield 2/1
2/1 - M, will yield a 2/2 square
Desert:
0/1 - M, will yield a 0/2 square (very handy)
Tundra (I believe):
1/0 - M, will yield a 1/1
Wheat Square:
3/0 or 4/0 - M/I, will yield one extra of whichever (I usually mine)
3/1 - M/I, will yield one extra of whichever (I usually mine - this is the ultimate square!)
4/1 - M/I will yield one extra of whichever
Game Square:
2/1 - M/I, will yield one extra of whichever (I usually mine)
2/2 - M/I, will yield one extra of whichever
Cattle/Peasant Square:
2/1 - M/I, will yield one extra of whichever
2/2 - M/I, will yield one extra of whichever
Mountain/Hill:
0/1 - M, will yield a 0/2 square
1/1 - M, will yield a 1/2 square
*note: if a mountain square has iron, it will yield a 0/4 square, which is the ultimate production square
As you can, most of the restrictions are on Grassland or Desert squares.
in my opinion, I just look at my city screen and this is how I judge whether or not to mine or irrigate:
If the turns to growth is more than double the amount of turns to build a warrior (or worker), irrigate. If it is less, mine.
There ye go, hope that helped. Any opinions would help.