Dave Cheney gave a great answer, with emphasis on the write penalties of SSDs.
If the drive is almost exclusively used for reads then SSDs rule absolutely. The "best" drives on balance, with a look at controller & firmware maturity, consistent performance, and price, are the Intel X25-M(ainstream) G2 and X25-E(xtreme).
The X25-E uses SLC flash, and is intended for server use. However, if the write load is very light, you could also consider the X25-M for heavier use. The main benefit of the E's SLC flash is much improved write endurance.
You can see the random 4kb block read performance of both drives here. Note the 10k RPM mechanical drive at the bottom of the chart. I'll quickly summarize Anantech's results:
- Random 4 KB reads aggregate transfer rate: Western Digital Raptor mechanical HD ~0.7 MByte/s, Intel SATA SSD ~50+ MByte/s.
- Random 4 kb reads average latency: WD Raptor ~17.3 milliseconds, Intel SSD ~0.21 milliseconds.
There are several interesting SSD drives on the way to market, but they're not quite ready yet. Intel isn't going to really update its SSDs before Q4 this year. So I'd go for the current Intel lineup, possibly attached to a (fast, modern) RAID controller.
Fusion I/O are the very fastest drives available right now. In the future I think performance SSDs will all use the PCI-Express bus, as Fusion I/O already does. Right now Fusion I/O is bloody expensive, but absolutely worth considering if your workload is transactional with a lot of writes.