0

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?

6
  • 1
    I have a table called "Types" If this list is static and won't be altered in future (or may be expanded till not more than 64 statuses) than this table seems to be excess. Simply make type_id column datatype = SET. This allows to store one row per user per day.
    – Akina
    Jan 28, 2021 at 10:44
  • Which query (top 3) do you want to be fast? (All others could be slow) Jan 28, 2021 at 11:03
  • @MichaelKutz User is login and he should see all records of him/her against each day. Jan 28, 2021 at 11:11
  • @Akina Types will be only 5, It will never increase. Jan 28, 2021 at 11:12
  • When do you remove data? How old? How often? Jan 28, 2021 at 11:20

2 Answers 2

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.

1
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.

2
  • Why u don't have ID column which is primary key to table? user_id is the foreign key of users table, why added as Primary key ? Feb 2, 2021 at 9:15
  • @AsfandyarKhan - I am assuming that the pair (user_id, date) is unique, hence acceptable as the PRIMARY KEY. Let's see the other table and the SELECT that need id. (It may not actually be needed.) Two-thirds of the tables that I build have a 'natural' PK and don't really need a surrogate AUTO_INCREMENT. Add id would increase the table size by several GB.
    – Rick James
    Feb 2, 2021 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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