Having the follow MyISAM table:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| player_id | int(11) | NO | PRI | NULL | |
| date | date | NO | PRI | NULL | |
| time | int(2) | NO | PRI | NULL | |
| minutes_online | decimal(5,0) | NO | | 0 | |
| minutes_playing | decimal(5,0) | NO | | 0 | |
| minutes_chatting | decimal(5,0) | NO | | 0 | |
| minutes_away | decimal(5,0) | NO | | 0 | |
+----------------------+-----------------------+------+-----+---------+-------+
Where:
date
will hold dates in the following formatYYYY-MM-DD
time
will hold the hour, from 0 to 23. Only the hour, not the minuteminute_[...]
will hold the minutes spent in a given hour of a given day. Naturally, from 0 to 60 and the sum of all minutes in a row can't be higher than 60.
Every minute the status of each player is checked and the table is updated accordingly. The number of players online is normally between 500 and 1200. Which means that data is written (with INSERT ... ON DUPLICATE KEY
) every minute and it can be hundreds or thousands rows a minute.
What about reads? Anyone (player or guest) can access the stats of any player (as long as the player doesn't hide it). The site has between 8,000 to 10,000 hits a day. To avoid reading a table that is constantly being written, I cache the main query (that pulls stats from last 6 months) for 1 hour. Caching with PHP, saving the array to a file. Meaning that to access the last 6 months of a given player, it will be only one read per hour.
However... there is AJAX. Players, not guests, can pick any day or any range of days, even older than 6 months, through AJAX. These queries are not saved into a file. Which means, if someone decides to check ranges or days multiple times, he will be hitting the table every time he pick a day/range.
This table has 16 millions of rows. There are other tables like this one with even more rows, but let's stay with this one. Querying the table sometimes takes a long time (about 15 seconds). This is why I cache the queries. And obviously, as this is MyISAM, the table gets locked every time a query hits the table.
So, considering all this, I came up with the following optimized version:
+----------------------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-----------------------+------+-----+---------+-------+
| player_id | mediumint(8) unsigned | NO | PRI | NULL | |
| date | date | NO | PRI | NULL | |
| time | tinyint(2) | NO | PRI | NULL | |
| minutes_online | tinyint(2) | NO | | 0 | |
| minutes_playing | tinyint(2) | NO | | 0 | |
| minutes_chatting | tinyint(2) | NO | | 0 | |
| minutes_away | tinyint(2) | NO | | 0 | |
+----------------------+-----------------------+------+-----+---------+-------+
Which will hold the same info, but using less space. Hoping that at least the queries can be a little faster.
Is this a better version? Would you suggest a different schema?
Would be better to use InnoDB?
EDIT
Sample query
Here are two queries that are run all the time.
To select all activity from the past 3 months grouped by day. This query is also used when the visitor picks a custom range.
SELECT
Stats.date,
SUM(Stats.minutes_online),
SUM(Stats.minutes_playing),
SUM(Stats.minutes_chatting),
SUM(Stats.minutes_away)
FROM
Stats
WHERE
Stats.player_id = '99999999' AND
Stats.date BETWEEN '2012-05-31' AND '2012-08-31'
GROUP BY Stats.date
And if the visitor wants to see the activity of a given day:
SELECT
Stats.date,
Stats.minutes_online,
Stats.minutes_playing,
Stats.minutes_chatting,
Stats.minutes_away
FROM
Stats
WHERE
Stats.player_id = '99999999' AND
Stats.date = '2012-05-31' AND Stats.time = '11'
Before changing to InnoDB, these queries, mainly the first one, used to take a very long time. Naturally, the table was locked most of the time. After changing it, the queries are much faster. INSERT/UPDATE are also faster.
I know that if happens to hundreds of visitors check the stats of the same player at the same time, the database may slow down. But this is a scenario not very likely given the traffic and because I cache the first query.