Yes HD speed DOES MATTER!
Even if you use your Amiga only for DTP or Gaming HD speed makes a difference.
Internet Browsers use cache and the faster the HD, the better the performance of the browser will be.
(unless you use a 3200 modem :-) )
Games load faster, Images load faster, Videos load faster - and even better, huge videos will paly really smooth!
As for SCSI, I believe that nothing is better than a Seagate Cheetah, but if you dont have the money for SCSI you could try UATA (or SATA). Today's IDE HDs are fast enough for any use.
