0

I am developing a test/exam solution where We have around 100K questions (MCQ / Objective Type). Each question belongs to a topic and each topic belongs to a subject.

Students practice these questions as part of weekly/monthly tests, but students can attempt questions just as practice workout and without any test.

Tables

subject

  • id
  • name

Topic

  • id
  • name
  • subject_id

Question

  • id
  • text
  • options columns
  • correct_option
  • difficulty

topic_questions

  • topic_id
  • question_id

question_attempts

  • user_id
  • question_id
  • was_correct //if user got this question right
  • attempt_date

Data size

  • Need to support around 100K questions
  • could have 100K users
  • question_attempt could go as big as 100K*100K or more.

Users subscribe and use the app over the period of 2 to 3 years during which period they take tests, practice questions as workouts etc. All of the students are competing for a single exam.

Use cases I need to support

  • Calculate question difficulty score based on success/failed attempts
  • Get list of unattempted questions for users to practice.
    • Each time user click next get an unattempted question for given topic, or subject.
  • Calculate score for user's strength/weakness for subject and topics based on user's question attempt success/fail
  • Get list of questions which user attempted last week but failed and let user practice them
  • Determine user's overall rank among other users based on his attempts

Major problem I see is : Finding unattempted questions, As it would need join between question and question_attempt which is very huge and querying last weeks failed question for user

For calculating topic/subject performance, i could do jobs which runs at midnight and could get away with lower performance.

Currently we use Mysql

2 Answers 2

3

If each user attempt each question only once, we will have 100K * 100K rows for attempts

I think it's unlikely every student would answer 100,000 questions, let alone even one student to do so. That's a lot of questions that would take someone 70 continuous days of testing, if each question was simple and took only 1 minute to read, analyze, and answer. Nonetheless, it doesn't change the answers to your questions anyway, so let's assume that's an accurate prediction of the future size of data.

Will mysql handle such large number of rows and still give decent performance for reads

Yes, size of data at rest doesn't change the read performance of the queries against that data, as long as the database is architected properly with appropriate indexes. This assumes you're not reading the entire table at one time, which is a size of data operation, and would be physically bound by the hardware of your server and irrelevant of whichever database system was used. (In that case, no different than moving a large file from one harddisk to another, for example.)

You'll find the more difficult problem is managing the database, from a schema changes and backups perspective. Again, this would be constant for any database system, and they each have their own features and solutions to alleviate this. Partitioning is one feature that can help with the management side of things.

Is mysql even ideal solution to store question attempt records ?

Sure. No different than any other modern relational database system. Whether a database system is an ideal choice for you, should more so be based on the features it offers, the price tag, the size of the community using it, and the support and documentation available for it. All relational database management systems are similar enough that the size of data shouldn't affect which one you choose.

If not mysql thn what ?

See above. Yes, it can be MySQL.

Optimal table / database design for this problem

More information is needed to answer this. You would need to provide more concrete examples of what the tables look like. But the table design itself will essentially mimic what makes logical sense as far as your objects, and not be anything too out of the ordinary to specifically cater for performance. Proper indexing will be key. But we'd also need some actual example queries provided as well, to help provide index examples.

And then this would all still be based on theory, until you actually implemented them in practice, tested the queries, ran with EXPLAIN ANALYZE enabled so we could observe their query plans, and then revise accordingly. Performance tuning is a multi-revision and generally ongoing process that needs to be based on the actuality of what the database engine is doing.

5
  • After implementing a prototype, let's discuss Summary Tables as a way to tame the future 10B rows.
    – Rick James
    Jun 1, 2023 at 14:16
  • @J.D each student prepare for this exam over the course of two/three years. And its highly likely for a student to attempt 50K/60K questions over this period. and its common
    – Sudhir N
    Jun 2, 2023 at 4:47
  • @SudhirN Still seems like an unbelievable amount, but again, doesn't really matter, it doesn't change the answer either way.
    – J.D.
    Jun 2, 2023 at 11:52
  • @RickJames I have updated question if you can provide suggestions for design
    – Sudhir N
    Jun 3, 2023 at 16:55
  • @J.D. please see updated question if that helps you provide more detailed design. Thanks
    – Sudhir N
    Jun 3, 2023 at 16:56
1

Calculate question difficulty score based on success/failed attempts

SELECT  question_id,
        ROUND(100 * SUM(was_correct) / COUNT(*)) AS success_percent
    FROM question_attempts
    GROUP BY question_id;

Get list of unattempted questions for users to practice.

SELECT  DISTINCT q.question_id
    FROM question AS q
    WHERE NOT EXISTS ( SELECT 1 FROM question_attempts AS qa
            WHERE qa.question_id = q.question_id );
    

Each time user click next get an unattempted question for given topic, or subject.

SELECT  question_id
    FROM  question AS q
    JOIN  topic_questions AS tq   ON tq.question_id = q.id
    JOIN  topic AS t  ON t.id = tq.topic_id
    LEFT JOIN  question_attempts AS qa USING(question_id)
    WHERE t.name = 'birds'
      AND qa.question_id IS NULL
    LIMIT 1;
    -- similarly for subject, etc.

Calculate score for user's strength/weakness for subject and topics based on user's question attempt success/fail

SELECT  question_id,
        ROUND(100 * SUM(was_correct) / COUNT(*)) AS success_percent
    FROM question_attempts
    GROUP BY question_id;
    ORDER BY success_percent DESC;
    -- or something like that

Get list of questions which user attempted last week but failed and let user practice them

    ...
    WHERE  NOT qa.was_correct
      AND  qa.attempt_date >= NOW() - INTERVAL 7 day

Determine user's overall rank among other users based on his attempts

-- This is more complex; get used to the other queries before attempting this one.
5
  • thanks for queries. But would this give decent performance. As the join or exists query between question attempts and question would involve two table one of which could have millions of row and another could have 100k rows. Could there be some alternative to improve performance
    – Sudhir N
    Jun 4, 2023 at 4:14
  • @SudhirN - No. As long as the necessary data is split between tables, you must use one of those constructs. Perhaps your computer is fast enough so it does not matter? There may be some way to summarize the info each night. This would lead to semi-stale data, but maybe that is good enough?
    – Rick James
    Jun 4, 2023 at 4:48
  • The list of unattempted question could be stored in a table. Then delete a row when the user answers a question. Very fast.
    – Rick James
    Jun 4, 2023 at 4:53
  • stale data for performance / scores / question difficulty is Ok. Need realtime results for unattempted questions only
    – Sudhir N
    Jun 4, 2023 at 5:08
  • @SudhirN - The extra table starts with a list of all questions. As the user attempts a question, you delete that one row from the table. Total: 1-row select of first row in table, + 1-row delete.
    – Rick James
    Jun 4, 2023 at 6:43

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.