I have been benchmarking a MyIsam MySQL database with some queries that'll regularly be performed. One simple table is this one, that is regularly accessed and contains unique URL strings:
CREATE TABLE IF NOT EXISTS `http_urls_list` (
`id` int(10) unsigned NOT NULL,
`url` text NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM
The table is just shy of 30 million rows and lookups to the `id` column are very fast, but any query that fetches the `url` column can be up to 10 times slower. I am looking into using SSD to store such tables to improve performance in fetching non-indexed data.
Does anyone have any views/experiences to share using SSD to improve database performance?
As an aside, I'm more interested in read performance. There is a separate `id`/`md5` table used for inserts that is only accessed when adding new urls.