I've never played it before, and I was considering getting the GOTY edition. But I'm used to playing OB maxed with texture packs, improved weather, etc.

What's the best total overhaul to improve sounds, graphics, AI, or whatever?

Is it http://planetelderscrolls.gamespy.com/View.php?view=Mods.Detail&id=8987?

Obviously the overhaul you linked to does only overhaul sounds and graphics. There is going to be a second Overhaul by Kingpix improving other things though.

What's the best to get now?

I have a sort of overhaul in the works, v 1.1 as we speak.

http://www.moddb.com/mods/morrowind-rebirth

It does not include the graphical elements (exluded a few tree mods from vurt) like The Sound & Graphics overhaul, but focuses on landscaping and cities.

For graphics and sound, the one you linked to.

For a gameplay overhaul, there isn't much. The closest thing I can think of is http://planetelderscrolls.gamespy.com/View.php?view=Mods.Detail&id=7904, and that doesn't change as much as an all out overhaul.

KINGPIX and BTB are still quite active in the community, which means there is now and will continue to be good support and quick bugfixes. The best advice I can give is to check and make sure the creator of whatever compilation you choose is still active, or at least answering his/her emails/PMs. If you can't get support for a Compilation, it's a little hard to get it from the individual modders as their mods have been altered for the compilation in ways they are not familiar with.