I don't know if you noticed this but many CPU coolers that are supposed to be the best and are recommended are freaking huge, like scythe mugen series, not only that but have even 2 fans.

Isn't that really disruptive to the airflow in the case and increases noise subtantially, they actually have the same amount and size of fans as the most cases themselves...
Just their size really repels me.

Hmm, kinda weird hearing that after several years of using ginormous CPU tower coolers.

Perhaps they do disrupt airflow a bit, but their advantage is mainly the use of a large 92/120/140mm fan that, combined with a large heatsink surface area, can spin slower and quieter than traditional stock heatsinks with their small fan and small heatsink surface area. Their size may repel you, but they're making good use of empty space above the CPU socket, that would otherwise go unused in an ATX tower case.

Basically, that front fan in most cases would just be for HDD/SSD then.

Hi, the point of two fans is that, for example you can get the same cooling with two fans at 700rpm as one at 1000rpm. Two at 700rpm will be quieter due to the way sound level works, and also lower in pitch, were human hearing sensitivity is lower too.
Most CPUs now, as long as not over-clocked, don't need massive towers. E.G. I used a Hyper-TX3 92mm fan tower on an i5-2500k system I built (stock clocks) and it could cool the CPU, even fully loaded, at low, quiet fan speed.
On the flip side my over-clocked i7-2600k needs everything I can wring out of massive NH-C14 twin 14cm fan cooler to cool it loaded quietly. Horses for courses...

Personally I don't bother with intake fans at all, just use a traditional -ve pressure system with exhaust fans and air is naturally drawn in the front of the case over the hard drive.
Seb

Had a Mugen 2 on a fairly cool CPU in my desktop. Huge tall rectangular tower of metal over the CPU. No fan on the CPU heatsink, depended on rear case fan and PSU fan (120 mm fan PSU) to draw air over it. Worked very well, airflow was beautifully straight lower front to upper back.

Generally the larger the heatsink the less you need to mess with the airflow to cool your CPU.

But this would only work with the latest Asus FanXpert 2 MBOs, wouldn't it? Because of on the fly automatic adjustment of fan speeds, so even if you have overclocked i5 3550 to 4GHz, it would still be near silent most of the time...

Using dual CPU cooler fans should work fairly well on any motherboard that has fan speed control, which is most now days. You would connect both fans to the CPU header and the board will adjust the fan speed to keep the CPU cool. As the two fans are more effective that one will settle at a lower point on the speed/temp curve than one fan. Admittedly it's not as good as full manual control, although many BIOSes (or UEFI) allow you to adjust the speed/temp curve and almost all boards can be over-ridden with software either from the manufacture or 3rd party, like Speedfan.

I've used Speedfan for a long time now, allows me to set what ever balance of fan speed to temp I like and link any temp to any fan speed. Currently I have it set so that CPU and case fans idle at 25%. If the CPU core exceeds 78°C then it jumps to 60% CPU with 45% case fans. If the CPU then exceeds 84°C it will go to 100% on all fans, and make quite a racket but doesn't do this unless very stressed and the house is hot. Speedfan can also read the graphics temp and I have this linked to the case fan speed only. It's quite flexible and well worth getting to grips with but I admit it's not obvious at all on first looking at it how to configure it.

Seb

Hopefully when Asus FanXpert 2 comes to my country I will be able to just set the desired idle and load temp and everything else is done automatic. I don't care much for constant fiddling.

What is preventing you from doing so now? What motherboard do you have?

Won't they be better for NOT disrupting the front to back airflow in most cases (compared to small top-down coolers).

Bigger fans also tend move the same amount of air with less noise.