1.3M queries/Hour. How would you construct the queries?

Question

I have an online iphone turnbased game, with lots of games running at the same time. I'm in the process of optimizing the code, since both me and the server have crashed today.

This is the setup:

Right now I have one table, "matches" (70 fields of data for each row. The structure), that keep track of all the active matches. Every 7 seconds, the iphone will connect, download all the matches in the "matches" table that he/she is active in, and update the UI in the iphone.

This worked great until about 1,000 people downloaded the game and played. The server crashed.

So to optimize, I figure I can create a new table called "matches_needs_update". This table have 2 rows; name and id. The "id" is the same as the match in the "matches" table. When a match is updated, it's put in this table.

Now, instead for search through the whole "matches" table, the query just check if the player have any matches that need to be updated, and then get those matches from the "matches" table.

My question is twofold:

1. Is this the optimal solution?

2. If a player is active in, say 10 matches, is there a good way to get those 10 matches from the "matches" table at the same time, or do I need a for loop doing 10 queries, one for each match:

   "SELECT * FROM matches WHERE id = ?"

Thanks in advance

Answer 1

You need to get out of the database. Look to memcache or redis.

Answer 2

I suggest APC...

...as you're on PHP, and I assume you're doing this from a single mysql database,

It's easy to install, and will be default from PHP 6 onwards.

Keep this 1 table in memory and it will fly.