1.3M requests / hour. How would you build queries?

Question

I have an iphone based online game with many games running at the same time. I am in the process of optimizing the code since today I and the server crashed.

This is the setting:

Right now I have one table that "matches" (70 data fields for each row. Structure) that track all active matches. Every 7 seconds, the iphone will connect, download all matches in the match table in which he / she is active, and update the user interface on the iphone.

This did a great job until around 1,000 people downloaded the game and played. The server crashed.

So, to optimize, I suppose I can create a new table called "matches_needs_update". This table has 2 rows; name and identifier. "Id" matches the match in the matches table. When a match is updated, it is placed in this table.

Now, instead of searching the entire matches table, the query simply checks to see if the player has any matches that need to be updated, and then get these matches from the matches table.

My question is twofold:

• Is this the best solution?

• If a player is active, say, 10 matches, is there a good way to get these 10 matches from the match table at the same time, or I need a for loop to execute 10 queries, one for each match:

  "SELECT * FROM match WHERE id =?"

Thanks in advance

Answer 1

I suggest APC ...

... like you are in PHP, and I assume that you are doing this from the same mysql database,

It is easy to install and will be used by default with PHP 6.

Keep this 1 table in memory and it will fly.

Answer 2

You need to exit the database. Take a look at memcache or redis.

Answer 3

Your database looks very small. A table with 70 rows should return within milliseconds, and even hundreds of queries per second should work without any problems.

A few traditional pointers

• Make sure you connect your connections. You will never have to connect when a client needs data.
• Make sure there is a pointer to “user in the match” so that the result is obtained from the index.
• I am sure that you have enough memory to store the entire structure in the cache, and with these small tables no additional configuration is required.
• Make sure your circuit is normal. One table for each user. One for each match. And one for each user in the match.

Answer 4

Cache startup time, such as memcache and apc.

As for loops, though coincidences ... this is the wrong way to do this.

How does a user connect to a match using a link table? or the correspondence table has something like player1, player2.

Quoting, although queries are not a way to index your tables correctly and make a join to display all active matches using userId if I were more efficient. Givin is the number of users you might also want (if you havent) to split the tables up for active and inactive games.

If 6,000 active games and 3,000,000 are inactive, it is extremely useful to split these tables.