Creating a cache for MySQL

Question

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.

Answer 1

What you are looking for is probably a read replica. This is a "slave" database that updates itself from the "master" database - you can perform your reads on the slave db, without interfering with the master db's performance.

See the mysql documentation for more details:

https://dev.mysql.com/doc/refman/5.1/en/replication-howto.html