I will cover some of the cooling solutions that are available for the processors on the market. I will only comment on the overclockers market since all other categories can use the cooler that comes with the CPU itself without any problems. Better cooling solutions are good only when overclocking. Of course if you feel unsafe with the temperatures that you see when using the stock cooler, you can get another cooler of your preference but that is useless most of the time and will not make much/any difference.
Air cooling solutions:
Air cooling is the most common method for cooling the CPU, although the advanced overclockers will probably prefer other methods.
Air cooling can be extremely noisy if extreme solutions are used. There are too many solutions to name them all, so I will just recommend those which are fitting that I recall offhand.
For average overclockers, the Thermaltake Volcano 7+ will do the work just fine. Those fit on both AMD and Pentium 4 processors and will perform well enough.
For advanced overclockers, they might prefer a ThermalRight SLK-900U with a fan of their choice, depending on how much noise they can tolerate. For extreme situations, the 92mm Vantec Tornado is the most fitting but will sound like a concorde jet. For good performance and low noise, the 70mm Ystech TMD or the 80mm ThermalTake SmartFan 2 which is adjustable are most fitting. The SLK-900U will fit on both AMD and Pentium 4 processors, however not on many motherboards. You should check over at ThermalRight website for the motherboards that are recommended for use with those heatsinks. There is also the Swiftech MCX line of heatsinks which are excellent, especially on the Pentium processors where they are considered the best.
Water Cooling solutions:
Water cooling is becoming common between advanced overclockers. However it is a vast topic to try and make recommendations from, to the point it turns to an art. Water cooling will surely be enough more expensive than air cooling and take a lot more space but will be silent and very effective as well. If you consider water cooling I suggest that you should read a few water cooling articles carefully and/or ask people with enough experience on the topic.
Phase Change solutions:
Phase change cooling solutions are expensive, very rare and they are extremely effective for overclockers. The most common among them are the Prometeia made by nVentiV (Chip-Con) and VapoChill made by Asetek. These can take the CPU temperatures down to 0c and lower easily, depending on the model and processor overclock/load.
nVentiv Prometia Mach II