Caching large amounts of content with PHP + MySQL
I am building an engine / CMS for web browser based games. I have quite a lot of data: characters, items and bits of history that the player will interact with. The goal of this project is that writers don't have to be programmers to create a story-driven web game. To start creating content, you only need a basic knowledge of FTP and site management.
The problem is I think the database will root for these games. Each character can have many, and the stories will be extensive. Each bit of history will have its own written text, which can be 100 characters or 500 characters long. There was no way I could have cached everything with memcached or something like that!
Fortunately, each state of the game is "pushed" through deployment, which means that you don't just add a character, you appear in the world; you have to add them and then click on build game. I believe I can use this to my advantage. Now my working concept is:
There will be three databases in total. One of them will be a "working" content database, the other will be a "live" content database, and then, finally, a database that contains all user data. (where they are in the story, the items they received, etc.). My idea is that I will push to the working DB, completely destroy the living and rebuild the living based on what is in the working DB during the push. The live DB will then use read-only capabilities, such as the ARCHIVE storage engine and quite a bit of indexing. This sounds pretty solid, but I am not experienced enough to be sure that this is the best way to do my business.
I would like to know if anyone has any suggestions for a new model or even suggestions for my current model.
source to share