WikiEduDashboard: Improve performance for slow queries

We enabled the slow query log feature on the outreachdashboard server to identify and optimize queries that take excessively long to execute. Currently, the log captures queries that run for more than one minute. The plan is to select a query, analyze it, optimize its performance, and submit a pull request with the improvements.

  • Alert monitor queries.
SELECT articles_courses.* FROM articles_courses INNER JOIN articles ON articles.id = articles_courses.article_id WHERE articles.title IN ('1946_Manchester_Borough_Council_election', ...) AND articles.wiki_id = 1;

Done on PR #6260

  • AverageViewsImporter#update_outdated_average_views query
# Time: 250319 19:09:17
# User@Host: outreachdashboard[outreachdashboard] @ peony-sidekiq-medium.globaleducation.eqiad1.wikimedia.cloud [172.16.6.194]
# Thread_id: 627  Schema: dashboard  QC_hit: No
# Query_time: 159.906835  Lock_time: 0.000077  Rows_sent: 85  Rows_examined: 15843645
# Rows_affected: 0  Bytes_sent: 10693
SET timestamp=1742411357;
SELECT DISTINCT `articles`.* FROM `articles` INNER JOIN `articles_courses` ON `articles`.`id` = `articles_courses`.`article_id` WHERE `articles_courses`.`course_id` = 22647 AND (`articles`.`average_views_updated_at` IS NULL OR average_views_updated_at < '2025-03-05 19:09:17.908465') ORDER BY `articles`.`id` ASC LIMIT 200;

Done on PR #6274

  • This query seems to be to calculate all the data for the Miscellanea campaign. We should better investigate where it comes from.
# Time: 250324 18:07:04
# User@Host: outreachdashboard[outreachdashboard] @ peony-web.globaleducation.eqiad1.wikimedia.cloud [172.16.6.223]
# Thread_id: 1191  Schema: dashboard  QC_hit: No
# Query_time: 164.954211  Lock_time: 0.000501  Rows_sent: 122535  Rows_examined: 688342
# Rows_affected: 0  Bytes_sent: 396367704
SET timestamp=1742839624;
SELECT `courses_users`.`id` AS t0_r0, `courses_users`.`created_at` AS t0_r1, `courses_users`.`updated_at` AS t0_r2, `courses_users`.`course_id` AS t0_r3, `courses_users`.`user_id` AS t0_r4, `courses_users`.`character_sum_ms` AS t0_r5, `courses_users`.`character_sum_us` AS t0_r6, `courses_users`.`revision_count` AS t0_r7, `courses_users`.`assigned_article_title` AS t0_r8, `courses_users`.`role` AS t0_r9, `courses_users`.`recent_revisions` AS t0_r10, `courses_users`.`character_sum_draft` AS t0_r11, `courses_users`.`real_name` AS t0_r12, `courses_users`.`role_description` AS t0_r13, `courses_users`.`total_uploads` AS t0_r14, `courses_users`.`references_count` AS t0_r15, `users`.`id` AS t1_r0, `users`.`username` AS t1_r1, `users`.`created_at` AS t1_r2, `users`.`updated_at` AS t1_r3, `users`.`trained` AS t1_r4, `users`.`global_id` AS t1_r5, `users`.`remember_created_at` AS t1_r6, `users`.`remember_token` AS t1_r7, `users`.`wiki_token` AS t1_r8, `users`.`wiki_secret` AS t1_r9, `users`.`permissions` AS t1_r10, `users`.`real_name` AS t1_r11, `users`.`email` AS t1_r12, `users`.`onboarded` AS t1_r13, `users`.`greeted` AS t1_r14, `users`.`greeter` AS t1_r15, `users`.`locale` AS t1_r16, `users`.`chat_password` AS t1_r17, `users`.`chat_id` AS t1_r18, `users`.`registered_at` AS t1_r19, `users`.`first_login` AS t1_r20, `courses`.`id` AS t2_r0, `courses`.`title` AS t2_r1, `courses`.`created_at` AS t2_r2, `courses`.`updated_at` AS t2_r3, `courses`.`start` AS t2_r4, `courses`.`end` AS t2_r5, `courses`.`school` AS t2_r6, `courses`.`term` AS t2_r7, `courses`.`character_sum` AS t2_r8, `courses`.`view_sum` AS t2_r9, `courses`.`user_count` AS t2_r10, `courses`.`article_count` AS t2_r11, `courses`.`revision_count` AS t2_r12, `courses`.`slug` AS t2_r13, `courses`.`subject` AS t2_r14, `courses`.`expected_students` AS t2_r15, `courses`.`description` AS t2_r16, `courses`.`submitted` AS t2_r17, `courses`.`passcode` AS t2_r18, `courses`.`timeline_start` AS t2_r19, `courses`.`timeline_end` AS t2_r20, `courses`.`day_exceptions` AS t2_r21, `courses`.`weekdays` AS t2_r22, `courses`.`new_article_count` AS t2_r23, `courses`.`no_day_exceptions` AS t2_r24, `courses`.`trained_count` AS t2_r25, `courses`.`cloned_status` AS t2_r26, `courses`.`type` AS t2_r27, `courses`.`upload_count` AS t2_r28, `courses`.`uploads_in_use_count` AS t2_r29, `courses`.`upload_usages_count` AS t2_r30, `courses`.`syllabus_file_name` AS t2_r31, `courses`.`syllabus_content_type` AS t2_r32, `courses`.`syllabus_file_size` AS t2_r33, `courses`.`syllabus_updated_at` AS t2_r34, `courses`.`home_wiki_id` AS t2_r35, `courses`.`recent_revision_count` AS t2_r36, `courses`.`needs_update` AS t2_r37, `courses`.`chatroom_id` AS t2_r38, `courses`.`flags` AS t2_r39, `courses`.`level` AS t2_r40, `courses`.`private` AS t2_r41, `courses`.`withdrawn` AS t2_r42, `courses`.`references_count` AS t2_r43 FROM `courses_users` LEFT OUTER JOIN `users` ON `users`.`id` = `courses_users`.`user_id` LEFT OUTER JOIN `courses` ON `courses`.`id` = `courses_users`.`course_id` WHERE `courses_users`.`course_id` IN (SELECT `courses`.`id` FROM `courses` INNER JOIN `campaigns_courses` ON `courses`.`id` = `campaigns_courses`.`course_id` WHERE `campaigns_courses`.`campaign_id` = 2 AND `courses`.`private` = FALSE) AND `courses_users`.`role` = 0 ORDER BY `courses_users`.`revision_count` DESC;
  • Download articles data in CSV format. This involves re-implementing CourseArticlesCsvBuilder.
SELECT `revisions`.* FROM `revisions` INNER JOIN `users` ON `revisions`.`user_id` = `users`.`id` INNER JOIN `courses_users` ON `users`.`id` = `courses_users`.`user_id` WHERE `courses_users`.`course_id` = 31437 AND (courses_users.role = 0) AND `revisions`.`system` = FALSE AND (date >= '2025-04-04 22:00:00') AND (date <= '2025-05-03 09:59:00') AND 1=1 AND `revisions`.`wiki_id` IN (1, 40, 71, 93, 117);
  • Download editors data in CSV format. This involves re-implementing CourseStudentsCsvBuilder.
SELECT `revisions`.`article_id`, `revisions`.`user_id` FROM `revisions` INNER JOIN `users` ON `revisions`.`user_id` = `users`.`id` INNER JOIN `courses_users` ON `users`.`id` = `courses_users`.`user_id` WHERE `courses_users`.`course_id` = 10001 AND (courses_users.role = 0) AND (date >= '2025-01-05 03:00:00') AND (date <= '2025-01-31 03:00:00') AND `revisions`.`new_article` = TRUE
  • Download overview data in CSV format. This involves re-implementing CourseCsvBuilder and PerWikiCourseStats.
SELECT COUNT(*) FROM `revisions` INNER JOIN `users` ON `revisions`.`user_id` = `users`.`id` INNER JOIN `courses_users` ON `users`.`id` = `courses_users`.`user_id` WHERE `courses_users`.`course_id` = 10001 AND (courses_users.role = 0) AND `revisions`.`system` = FALSE AND (date >= '2025-01-05 03:00:00') AND (date <= '2025-01-31 03:00:00') AND 1=1 AND `revisions`.`wiki_id` = 1 AND `revisions`.`wiki_id` = 1
SELECT COUNT(*) FROM `revisions` INNER JOIN `users` ON `revisions`.`user_id` = `users`.`id` INNER JOIN `courses_users` ON `users`.`id` = `courses_users`.`user_id` INNER JOIN `articles` ON `articles`.`id` = `revisions`.`article_id` WHERE `courses_users`.`course_id` = 10001 AND (courses_users.role = 0) AND `revisions`.`system` = FALSE AND (date >= '2025-01-05 03:00:00') AND (date <= '2025-01-31 03:00:00') AND 1=1 AND `revisions`.`wiki_id` = 1 AND `articles`.`namespace` = 0

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Comments: 15 (15 by maintainers)

Most upvoted comments

I think the third query is the same thing that I worked around with this change https://github.com/WikiEducationFoundation/WikiEduDashboard/commit/2755f260e190707296724fd60f55acb9665c2ead

If there’s a nice way to make the query more efficient, it’s still worth doing, but it might not be a source of really slow queries any more.

Cool. Feel free to open a PR with the migration to add the index. I will need to make a copy of the DB and test how long the migration takes with a large ArticlesCourses table, to make sure it’s safe to migrate in production.

@Abishekcs thanks! This looks very promising. Is there a straightforward way to implement it?

Update: I’ve found a solution for the second issue. I’ll explain the approach later and then open a PR. Currently testing a few more things.