Hi All,
I have a huge data in mdl_user_enrolments table. More 80 lakhs data. This is slowing down my system. How to do table partion in moodle or please provide any alternative method to load the data faster.
Thanks in Advance
Regards
Vysakh
Moving to hardware and performance forum...
However, how did you establish that this table was slowing down your system?
I mean the page is not loading, Its taking a lot of time to load the data. Please suggest method to load data faster in my page.
Below given is the query I am using to load my data
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
Did *you* write that query? Where is it (in some custom code)?
The first step is normally to use 'explain' to see what indexes it is using (or not). If this is custom code and it's slow due to a lack of appropriate indexes in Moodle tables then it gets tricky. Do you just add the indexes or do you rewrite or split up the query to take advantage of existing Moodle indexes? I probably prefer the latter as it is more sustainable.
However, I tried this on my (very big - mdl_user_enrolments has around one million rows) Moodle site and it was quick and fully indexed...
+----+-------------+-------+--------+-----------------------------------------------------------------+---------------------+---------+---------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------+---------------------+---------+---------------------+------+-------+
| 1 | SIMPLE | t1 | ref | mdl_userenro_enruse_uix,mdl_userenro_enr_ix,mdl_userenro_use_ix | mdl_userenro_use_ix | 8 | const | 4 | |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY,mdl_enro_cou_ix | PRIMARY | 8 | moodle2.t1.enrolid | 1 | |
| 1 | SIMPLE | t3 | ref | mdl_quiz_cou_ix | mdl_quiz_cou_ix | 8 | moodle2.t2.courseid | 4 | |
+----+-------------+-------+--------+-----------------------------------------------------------------+---------------------+---------+---------------------+------+-------+
...so it's hard to see what your problem might be.
Yes Its custom query, and I have total of thirty millon data, in mdl_user_enrolments table. After login I am directly executing query using my function and loading the result. But its talking a lot of time to return the result.
I am executing one query inside the for loop of this result to get result from mdl_course_categories
select
t1.id AS country_id,
t1.name AS country_name,
t2.id AS package_id,
t2.name AS package,
t3.id AS section_id,
t3.name AS section_name,
t4.id AS class_id,
t4.name AS class_name,
t5.id AS subject_id,
t5.name AS subject from
mdl_course_categories t1
join mdl_course_categories t2 on t1.parent = 2 and t2.parent = t1.id
join mdl_course_categories t3 on t3.parent = t2.id
join mdl_course_categories t4 on t4.parent = t3.id
join mdl_course_categories t5 on t5.parent = t4.id
join mdl_course t6 on t6.id='".$procedure[0][$i]['course_id']."' and t6.category = t5.id
I have category structure as given below.
Country
country1,country2, country3
Basic ,Classic, Premium (subscription models)
Primary, Secondary
standard 1 to standard 8
subject1 to subject7
three courses in each subject
6 exams are there in each course
I have checked the indexing and all and its proper. Same query I used for webservice in Android and iOS App its working fine and loading fast