We have a table with 54 million records. Here is the table structure.
CREATE TABLE `metaplay` ( `track_id` int(11) NOT NULL DEFAULT '0', `user_id` int(11) DEFAULT NULL, `completed` int(11) DEFAULT NULL, `skipped` int(11) DEFAULT NULL, `created` int(11) DEFAULT NULL, `updated` int(11) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), KEY `created` (`created`), KEY `updated` (`updated`), KEY `skipped` (`skipped`), KEY `track_id` (`track_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1
All these data are numeric. At this point, we have ~300 inserts and ~100 updates per minute. We also pull out daily, weekly and monthly track playing records from this table. Now my question is which of the followings will be better for architectural design and best performance. I would appreciate if you can highlight hardware details as well.
- MySQL in SSD Cached Storage with 4GB Ram
- MySQL in SSD with 4GB Ram
- Any NoSQL Solution
- Anything else?
Also do you suggest any mysql specific tuning tips for a table like this?