I'm looking to create a cache for MySQL.
The issue is I have a relatively large table (hundreds of millions of rows) and there are a lot of fields associated with each row. The table gets probably around 1,000 to 2,000 records per minute. Very seldom do old records ever get accessed.
I only want to retrieve around a thousand rows at a time between two times or IDs. The database is located on another server and I am thinking it might be better to have a cached "database" thing on the server I'm currently on. I only want to cache items like a few IDs, the value, and the timestamp.
Is there some type of cache I can use for this purpose? Perhaps something that will automatically update itself periodically and automatically connect to MySQL.
I already had Sphinx installed on this server, so I created it as a source with delta indexing, but it takes forever to reindex.