search

gjbarnard / moodle-format_grid

Grid course format contributed by Gareth J Barnard originally created by Paul Krix
GNU General Public License v3.0
21 stars 55 forks source link

Add index to courseid format_grid_icon could improve performance #121

Closed yao9394 closed 4 years ago

yao9394 commented 4 years ago

xxxx-moodle=> explain analyze SELECT sectionid, image, displayedimageindex FROM mdl_format_grid_icon WHERE courseid = 17934; QUERY PLAN

Seq Scan on mdl_format_grid_icon (cost=0.00..3755.45 rows=14 width=36) (actual time=0.005..15.729 rows=17 loops=1) Filter: (courseid = 17934) Rows Removed by Filter: 146619 Planning time: 0.033 ms Execution time: 15.741 ms (5 rows)

xxxx-moodle=> create index concurrently mdl_formgridicon_course on mdl_format_grid_icon(courseid); CREATE INDEX xxxx-moodle=> explain analyze SELECT sectionid, image, displayedimageindex FROM mdl_format_grid_icon WHERE courseid = 17934; QUERY PLAN

Index Scan using mdl_formgridicon_course on mdl_format_grid_icon (cost=0.42..54.05 rows=14 width=36) (actual time=0.023..0.026 rows=17 loops=1) Index Cond: (courseid = 17934) Planning time: 0.110 ms Execution time: 0.036 ms (4 rows)

This will shave 15ms of a number of our page loads.

gjb2048 commented 4 years ago

@yao9394 Thanks for the patch, so is that 15ms shaved off when there are only 14 rows in the table?

gjb2048 commented 4 years ago

@yao9394 What database engine are you using?

gjb2048 commented 4 years ago

Ok, going to take a punt. But when merge into M3.9 version, I'm going to leave the version the same as this is unreleased and the version jump in the file does not match the increment that I use. Therefore you'll need to downgrade by altering your DB version and config table with the 'allversionhash' to fool Moodle into updating.