Your proposed design is completely viable.
Couple of things to consider:
- the table most likely will be write (INSERT) heavy
- will grow indefinitely unless archived
- queries most likely fall under OLAP and not OLTP (aggregations over point queries)
Assuming InnoDB engine you want primary key to be a continuously incrementing id so INSERT won't be random causing too much page split and fragment the tablespace.
- Always incrementing
- Providing fast insert rate
- One extra column
- You probably will never query on PK
Timestamp would be a great candidate for PK if you can keep it
UNIQUE since it's most likely will participate in most of your queries and most like as a range. (PK is at the end of every secondary key).
You could also experiment with the an
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY instead. Use whichever gives better performance.
Try to keep the row size as small as possible to avoid sharding/partitioning/archiving as long as possible (eventually you will need to but MySQL is quite good with tens of millions of rows even).
To achieve this use only IDs in this table and have lookup tables where necessary. For example: instead of
action VARCHAR(12) use
action_id TINYINT UNSIGNED.
TINYINT is 1 byte whereas action will be always at least 2 assuming
latin1 or similar small character set but longer the string becomes the more byte it will use obviously.
UTF8 can use up to 4 bytes / character.
Only put index on columns where you really need to. Daily, monthly, etc aggregation tables will serve you better than heavy indexes on this table.
A possible schema
CREATE TABLE my_action_log (
ts TIMESTAMP(3) PRIMARY KEY,
action_id TINYINT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
post_id INT UNSIGNED NOT NULL,
KEY ... # Whatever index you need