MySQL DB Optimization

I am working on a table where compared data of colleges have been stored. But facing difficulty to query data in low response time. I am creating an API to get some analytics data within 300ms but a simple query on this table takes more than 1-2 sec.

I am thinking of re-design the table or create another table in different format. Tried multiple ways but couldn't find the right solution.

Tech stack - MySql, Django, DRF

Table name - compared_college.

Columns - id, college_1, course_1, college_2, course_2, college_3, course_3, college_4, course_4

So basically each college has a course which are compared. Max 4 college-course combination can be compared at once and there entry goes into this table.

Each course is mapped to a college and a domain and level. But a college can b mapped to multiple course.

Master Tables and column-

College - id, name, published

Course - id, name, published, domain, level

Now compared_collge has more than 40lakh rows.

I want some analytics data -

  1. Top 10 most compared colleges based on domain or level or without domain-level filter.

  2. Top 10 most compared combination of college (pair) based on domain or level or without filter.

  3. Top 10 most compared course.

Note - always consider published course and college. And data should be real-time where any college or course can be unpublished.

I am working on django to create API.

Вернуться на верх