I'll list the expansions in the order they were released, and give you some basic information and my experiences with them.
- Curse of the Dark Pharaoh - The first expansion, a "small box" expansion that just adds new cards. Out of all of the expansions, this one (currently) adds the smallest amount to the game. It adds a few new mechanics in the Exhibit Items (which are a different class of Unique Items), being able to be locked out of a neighbourhood, and the benefits and detriments. There's a revised version coming out during the summer of 2011, so if you were planning on getting it, you may want to consider waiting until then, as it will completely replace the old version.
- Dunwich Horror - This was the first "big box" expansion that adds to the board. It also adds 8 new investigators, 4 new Ancient Ones, and some new mechanics (Injuries and Madnesses, conditions, Vortex Spaces, Stalkers) and monsters and whatnot. It's a pretty fun expansion, in my opinion. The Dunwich Horror itself is a fun encounter, and the new location fits in well and adds to the board well.
- The King in Yellow - Another "small box." It adds Heralds (which are kind of like mini-Ancient Ones that add debuffs or other challenges to the game, you know, if things are going to easy for you) and persistent Magical Effects. It also adds Blights, and has a cool Acts and Yellow Sign mechanics, which can really amp up the intensity of the game.
- Kingsport Horror - A "big box" that adds to the game board again, and also comes with new investigators and Ancient Ones. It also includes some new mechanics, like Rift gates (movable Gates), Epic Battles (which are cards that add a little more structure and excitement to battles with the Ancient Ones), and Guardians (the opposite of Heralds, these provide mechanics and buffs to help the investigators). While I like the Guardians and the Epic Battles, Kingsport itself is incredibly annoying. You pretty much have to have at least one investigator up there, running around doing the equivalent of busywork to keep Rifts from popping up keeping it under control.
- The Black Goat of the Woods - A "small box" expansion that introduces Difficulty cards (things too easy last time? Play at a harder difficulty as described by these cards), Corruption (effects triggered by other effects in the game, like monster movement), and Cult encounters. You can even join the cult. I wasn't a big fan of this box, but I know a lot of other players love it. I just didn't find it really added anything to the game.
- Innsmouth Horror - This is my favourite expansion. It's a "big box" that adds to the board, as well as adding new investigators, a whole whack of Ancient Ones (8), two more Heralds, the aquatic movement mechanic, and Personal Stories, which are a lot of fun. They're kind of meta-game missions for each character that can add some cool mechanics or enhance a character if successfully completed... or really hinder a character if failed. They can detract from the main goal of the game, however, so you have to be careful. Innsmouth is an interesting place with its own mechanics that don't feel like busywork like Kingsport. The Ancient Old ones here are also quite interesting, with their own mechanics and challenges.
- The Lurker at the Threshold - The latest "small box" expansion, this adds Gate tokens with new mechanics that replace the existing Gates in the game, Relationship cards which can change how your investigator relates to the other investigators in the game (in both positive and negative ways), and the Lurker at the Threshold herald, who can be a help or a hinderance. He likes to help you succeed at casting spells... but there may be a price to pay. If you don't tend to play spell casters, it's also completely ignorable.
Spring 2011 will see the release of the Miskatonic Horror expansion, which is a big box expansion that adds to all of the other expansions.
As for which to get, if you can only get a few, I'd recommend Innsmouth Horror, the King in Yellow, and Kingsport Horror, if only for the Epic Battles.
I have all of the expansions to date, and have played them all pretty extensively (except for the Black Goat of the Woods), and they all have their pros and cons. I would starts with those three, however. You can play with all of the expansions at once, but be prepared that you will have a lot of things to keep track of. Not to mention that you'd need a very large table and that the balance would require a large number of investigators.