Optimizing table and choosing storage engine

Question

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 format YYYY-MM-DD
• time will hold the hour, from 0 to 23. Only the hour, not the minute
• minute_[...] 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.

Answer 1

Your second version is certainly better. To further improve...

TINYINT --> TINYINT UNSIGNED (yeah, this does not really make any difference; just cleaner)

DATE + TIME --> DATETIME or TIMESTAMP ...

• It is almost always a bad idea to split DATE and TIME; it is so much easier to split a DATETIME than it is to put it back together.
• Sizes: DATE:3, time as TIMYINT:1, DATETIME:8, TIMESTAMP:4
• You can play games to turn a date&time into a DATETIME or TIMESTAMP representing the beginning of the hour (which you appear to need).

MyISAM vs InnoDB --

• InnoDB recovers better from crashes
• Unlike the 'wisdom' of a decade ago, InnoDB is likely to be faster.
• InnoDB will take 2x-3x the disk footprint. 16M rows --> under a GB? Yawn.

Long queries, long lock times -- Well, let's see the naughty queries. Sounds like they would benefit from "summary tables". Offhand, I would suggest subtotals for each day. Such a table would be upwards of 24x smaller, hence faster if you need a 'table scan'. (TINYINT --> SMALLINT)