You do have a few different issues here. First how much of an investment are you willing to put into this older system? This is only a Core Duo based system so it won't run newer versions of Apps as its limited to 32bit apps and most are migrating to 64bit running under the newer 64bit OS's (review the link I posted in your question).
While the type of drive you have in your system is a factor of performance, memory can also effect performance as well as drive maintenance. If your drive is getting quite full I would first think about either cleaning things out or going with a bigger drive. You do want to up to 1/3 of the space free on these smaller drives. More so when you have a limited amount of RAM (1 or 2 GB) like in this series.
While I love SSD's, I do wonder if they make sense in these older systems. You see you face the dreaded compatibility issues many SSD's have as most don't support the older SATA I (1.5 Gb/s) interface in your system. While you can still find them I think you'll find going with a SSHD hybrid drive a better direction. The other factor here is costs, Yes, the smaller SSD's are more economical but again you do need to watch how full your drive is as these systems limit in memory requires more free space on the drive. Lastly, the OS you have doesn't offer TRIM services. While there is a 3rd party which offers a solution I'm not sure if they are still in business. There are a few SSD's which have their own cleanup services builtin now can you find the right SSD that works in your system??
I would recommend sticking with the SSHD hybrid drive (Seagate) Mayer has pointed you to. It will run in your system without any problems. A SSHD offers the benefits of being a traditional disk for the bulk storage (HD) and a deep SSD cache to speed up system boot up and running apps.
But before you jump into getting a new drive how about booting up under the grey CD/DVD your system came with and run Disk Utility to see if it has any problems and then try defrag'ing the drive using a good file defragmentation utility like: Drive Genius You'll need to ask them for the older version that runs on Snow Leopard. See if that improves things. Lastly, look at your RAM have you maxed it out to 2 GB? Which sadly is the limit of this system.