Database design for bigger tracking table

Question

I am creating a database for a prayer tracker. There are 5 types of prayer for each day. I have a table called "Types"

id, name
1, Type 1
2, Type 2
3, Type 3
4, Type 4
5, Type 5

I want to save records for each user against each prayer type for each day. Below is my structure for tracking table.

id, type_id (FK of Types), user_id (FK of user table), date (Y-m-d)

I have 500K+ users and for each user there can be max of 5 records against each day. This will be in millions. Basically I want to create an optimized db structure, So it should be faster while doing queries.

• User is login and he should see all records of him/her against each day.
• After 1 year, if there are 5 records against each day then for
  500K users, it will be 912,500,000 records. Table will be much
  bigger.
• No. of users are also increasing day by day.

So In order to optimize the database, what will be best practice for above structure of tracking table?

Answer 1

Honestly, 912 million records may seem daunting but it's nothing that regular indexing won't be able to handle, especially since your main query is to return only the current day's prayer types for the current user which is a very small amount of data. I've managed Tables with 10s of billions of rows in them and proper indexing allowed us to return small sets of data in milliseconds runtime, on very modest hardware. And I'm sure Tables in the 100s of billions would've been fine as well. Note this was using SQL Server instead of MySQL but the principals are the same on how indexing works in regards to storing and fetching data.

At your current rate (no user growth) for example, you wouldn't hit 100 billion rows for about 100 years. If you're able to implement Akina's suggestion then you'll be able to cut the amount of data down by 80% too. So that 100 billion rows now becomes only 20 billion in 100 years. (In other words, it withstands the test of time. :) If you're able to archive data after X number of years you can also alleviate the data load of your Table.

Finally in regards to indexing I think to support a query that will select all prayer type records for a given user only on a given day, you should create the index on user_id, date (and depending on which MySQL engine you're using include type_id if it's not by default) for best indexing.

Answer 2

CREATE TABLE blah(
    user_id MEDIUMINT UNSIGNED NOT NULL,  -- 3 bytes
    date DATE NOT NULL,  -- 3 bytes
    prayers SET('1','2','3','4','5') NOT NULL,  -- 1 byte
    PRIMARY KEY(user_id, date)
) ENGINE=InnoDB;

will occupy about 30GB (including overhead).

You may need other indexes, depending on what the queries are.