I am using the below given custom query in my moodle site and its working fine but now the total records in mdl_user_enrollments table is 30 million which take time to return the result to my frond end screen. Please let me know how to make it fast.
Query given below
select t1.userid,t2.courseid AS course_id, t1.timestart AS subscription_start, t1.timeend AS subscription_end,t1.timecreated AS timecreated,t1.timemodified AS timemodified, t3.id AS quiz_id,
t3.name AS quiz_name from mdl_user_enrolments t1
join mdl_enrol t2 on t1.userid=$user_id and t1.enrolid = t2.id
join mdl_quiz t3 on t3.course = t2.courseid
Explain output
Explain mdl_user_enrolments result
Field Type Null Key Default Extra
id bigint(10) NO PRI NULL auto_increment
status bigint(10) NO 0
enrolid bigint(10) NO MUL NULL
userid bigint(10) NO MUL NULL
timestart bigint(10) NO 0
timeend bigint(10) NO 2147483647
modifierid bigint(10) NO MUL 0
timecreated bigint(10) NO 0
timemodified bigint(10) NO 0
Analyze table mdl_user_enrolments result
Table Op Msg_type Msg_text eLearningDev_v1.mdl_user_enrolments analyze status OK
Size of table mdl_user_enrolments 910 MB
Select t1.userid,t2.courseid AS course_id, t1.timestart AS subscription_start, t1.timeend AS subscription_end,t1.timecreated AS timecreated,t1.timemodified AS timemodified, t3.id AS quiz_id,
t3.name AS quiz_name from mdl_user_enrolments t1
LEFT join mdl_enrol AS t2 on t1.userid=$user_id and t1.enrolid = t2.id
LEFT join mdl_quiz AS t3 on t3.course = t2.courseid
Hopefully this will works for you.