Query latency when fetching and updating records from a moderately sized table (~15k–50k rows)

I have an online testing platform where 50 questions are inserted into a single table per user session, and that same table updates with their responses and scores. With around 15,000 rows, fetching the next question and recording answers has started lagging. Is tens of thousands of rows enough to cause this latency on its own, and what are the best steps to troubleshoot and resolve it?

The size of the table isn’t particularly relevant, but the number of records returned and structure of your search expression is. Can you send a screenshot of your search/actions?

What’s the full expression? Not just the constraints.

Search for Test’s : random item

and then the constraints in the screenshot above.

How much time does it take to return the results?

And, why are you returning all the 15K records? Is that for the Admin? From what I understand you have a single test with 50 questions and you record answers per question for each user who takes the test? Is that correct?

One thing isn’t quite clear to me.

You have a table with 15,000 questions (Table 1), then you randomly select 50 of those questions and create a new table for the user (Table 2). Is that correct?

How exactly are you doing that? Are you looping through those 50 questions and creating them one by one in Table 2?

And if you’ve already created Table 2 with those 50 questions, I’m not sure I understand why Table 1 (the one with all 15,000 questions) is still involved when loading the next question. Aren’t all the questions the user needs already in Table 2?

I feel like I’m missing something about how this flow works.

Hey, apologies for such a late reply. I have a Question table with 1500 records from which I choose 50 random questions for the user. I load these 50 Questions in User_Question table where I record the response from user for that question and score for that question. So over a period of time the number of records in User_Question goes on increasing. And since next question on UI has to be fetched from this table I suspect in future it may create delay.

Hey, apologies for such a late reply. I have a Question table with 1500 records from which I choose 50 random questions for the user. I load these 50 Questions in User_Question table where I record the response from user for that question and his/her score for that question. So over a period of time the number of records in User_Question table goes on increasing. And since next question on UI has to be fetched from this table I suspect in future it may create delay.

Your biggest problem is that your search runs on 15k entries and then extracts 50 questions, what I will recommend is to break them down into categories and sub categories

When extracting for users, you run the random search on you category levels, e.g.
15000 entires → 15 categories → 10 subcategories each → assume 300 questions each

when user interacts, you extracte a random category, then select a sub category and then search for questions in that subcategory

You will need to loop this to run a few times to extarct 50 questions, but that should solve your initial problem

I am selecting 50 questions from 1500 records and not 15000 records. Also, these 1500 records are divided into different categories which I use during selection.

These 50 questions then I load into another table where I need to capture and save the user response for these 50 questions. This is where I might face the delay. Because over time this table will go on accumulating new questions for new users.

As long as you have a related field for question, related field for user and a field for answer it should be fine.