Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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)

share|improve this answer
    
My only objection is regarding the splitting of date and time. First, the name "time" is not appropriate here, it should be "hour" as it holds a nukber from 0 to 23. So, DATE+HOUR = 4 bytes. Anything else will have the same or more bytes and more complex retrieval queries (e.g. to convert timestamp to date+hour) –  ypercube Aug 31 '12 at 12:08
    
And various queries may require indexing that can be done easier in MySQL when the date and time are split. –  ypercube Aug 31 '12 at 12:18
    
I've updated the question with two common queries. I've completed copying all the data a couple of days ago. I have now two tables. I'm still inserting/updating on both, in case I decide to change back. But the live data are using the InnoDB table. Inserting, updating and selecting are now faster. I won't mess with the DATE+TIME for now. I will let it run for some time and see how it works out. –  rlcabral Aug 31 '12 at 14:59
    
Do you have PRIMARY KEY(player_id, date, time)? It needs to be in exactly that order in order to best support those two SELECTs. –  Rick James Aug 31 '12 at 23:13
    
@RickJames yes. They are in that order. –  rlcabral Sep 2 '12 at 14:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.