MySQL will use as many processors as there are concurrent queries. Wikipedia, for example, currently buys dual CPU dual core Opteron boxes, with six 15,000 RPM SCSI drives in RAID 0 (it has mirrored systems via replication, not mirrored drives), a write caching disk controller (to speed up transaction commits) and 16GB of RAM.
If your load is mostly one long-running query, the faster single CPU will be best. If it's lots of queries running at the same time more cores will get you more performance. I'd usually go with more cores and cheaper CPU speeds - middle or bottom of the speed range instead of top for best price-performance.
For systems with lots of data the limit is usually IO speed (disk seeks), not CPU.
15,000 RPM drives really do deliver enough extra performance to justify their price when it comes to transaction commit rate. For InnoDB, that means putting the InnoDB logs on the fastest drive possible, even if the rest is on a slower but larger and cheaper drive or set of drives. Write caching disk controllers are how you go faster than that for transaction commit rate.
For the never-ending SCSI or SATA discussion, I put together identical boxes except one had six Seagate 10,000 RPM SCSI drives and the other six WD 10,000 RPM SATA (not NCQ). The SATA system was usually faster in real life but suffered a bit more from replication lag and was slower when there was a high write load going on. NCQ would probably give the current SATA models more help and they might now stay ahead of the SCSI drive. Maybe - I haven't tested. Since replication lag annoys people I went with SCSI for later buys because there was a significant replication load. And because you can only get 15,000 RPM in SCSI and it is worth having if you have the load to justify it.
