Add pagination to `Profile > Contributed projects` page on User Profile
Zendesk issue: https://gitlab.zendesk.com/agent/tickets/32011
Customer reported seeing a slow query on their database and traced it back to the user profile contributed projects page (Ex. https://gitlab.com/users/dblessing/contributed). The query seems to read a lot of rows, perhaps unnecessarily. Can we optimize this?
Version: 8.10
Query:
# Time: 160801 14:29:43
# Thread_id: 17041693 Schema: gitlabhq_production_tools05 Last_errno: 0 Killed: 0
# Query_time: 1.345115 Lock_time: 0.000667 Rows_sent: 1 Rows_examined: 14816247178613373493 Rows_affected: 0 Rows_read: 14816247178613373493
# Bytes_sent: 63 Tmp_tables: 2 Tmp_disk_tables: 0 Tmp_table_sizes: 2093072
# InnoDB_trx_id: D1016E0B
SET timestamp=1470032983;
SELECT COUNT(*) FROM `projects` WHERE `projects`.`pending_delete` = 0 AND (projects.id IN (SELECT `projects`.`id` FROM `projects` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `projects`.`id` IN (SELECT `projects`.`id` FROM `projects` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND (projects.id IN (SELECT `projects`.`id` FROM `projects` INNER JOIN `namespaces` ON `projects`.`namespace_id` = `namespaces`.`id` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `namespaces`.`owner_id` = 2 AND `namespaces`.`type` IS NULL
UNION
SELECT `projects`.`id` FROM `projects` INNER JOIN `namespaces` ON `projects`.`namespace_id` = `namespaces`.`id` INNER JOIN `members` ON `namespaces`.`id` = `members`.`source_id` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `namespaces`.`type` IN ('Group') AND `members`.`source_type` = 'Namespace' AND `members`.`user_id` = 2 AND `members`.`type` IN ('GroupMember') AND `members`.`requested_at` IS NULL
UNION
SELECT `projects`.`id` FROM `projects` INNER JOIN `members` ON `projects`.`id` = `members`.`source_id` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `members`.`source_type` = 'Project' AND `members`.`user_id` = 2 AND `members`.`type` IN ('ProjectMember') AND `members`.`requested_at` IS NULL
UNION
SELECT `project_id` FROM `namespaces` INNER JOIN `project_group_links` ON `project_group_links`.`group_id` = `namespaces`.`id` INNER JOIN `projects` ON `projects`.`id` = `project_group_links`.`project_id` AND `projects`.`pending_delete` = 0 INNER JOIN `members` ON `namespaces`.`id` = `members`.`source_id` WHERE `members`.`source_type` = 'Namespace' AND `namespaces`.`type` IN ('Group') AND `members`.`user_id` = 2 AND `members`.`type` IN ('GroupMember') AND `members`.`requested_at` IS NULL)))
UNION
SELECT `projects`.`id` FROM `projects` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `projects`.`visibility_level` IN (20, 10))) AND (namespace_id != 41);
# Time: 160801 14:29:44
# Thread_id: 17041693 Schema: gitlabhq_production_tools05 Last_errno: 0 Killed: 0
# Query_time: 1.363422 Lock_time: 0.000486 Rows_sent: 4 Rows_examined: 15571755233472196589 Rows_affected: 0 Rows_read: 15571755233472196589
# Bytes_sent: 6321 Tmp_tables: 2 Tmp_disk_tables: 0 Tmp_table_sizes: 4186144
# InnoDB_trx_id: D1016E2A
SET timestamp=1470032984;
SELECT `projects`.* FROM `projects` WHERE `projects`.`pending_delete` = 0 AND (projects.id IN (SELECT `projects`.`id` FROM `projects` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `projects`.`id` IN (SELECT `projects`.`id` FROM `projects` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND (projects.id IN (SELECT `projects`.`id` FROM `projects` INNER JOIN `namespaces` ON `projects`.`namespace_id` = `namespaces`.`id` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `namespaces`.`owner_id` = 2 AND `namespaces`.`type` IS NULL
UNION
SELECT `projects`.`id` FROM `projects` INNER JOIN `namespaces` ON `projects`.`namespace_id` = `namespaces`.`id` INNER JOIN `members` ON `namespaces`.`id` = `members`.`source_id` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `namespaces`.`type` IN ('Group') AND `members`.`source_type` = 'Namespace' AND `members`.`user_id` = 2 AND `members`.`type` IN ('GroupMember') AND `members`.`requested_at` IS NULL
UNION
SELECT `projects`.`id` FROM `projects` INNER JOIN `members` ON `projects`.`id` = `members`.`source_id` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `members`.`source_type` = 'Project' AND `members`.`user_id` = 2 AND `members`.`type` IN ('ProjectMember') AND `members`.`requested_at` IS NULL
UNION
SELECT `project_id` FROM `namespaces` INNER JOIN `project_group_links` ON `project_group_links`.`group_id` = `namespaces`.`id` INNER JOIN `projects` ON `projects`.`id` = `project_group_links`.`project_id` AND `projects`.`pending_delete` = 0 INNER JOIN `members` ON `namespaces`.`id` = `members`.`source_id` WHERE `members`.`source_type` = 'Namespace' AND `namespaces`.`type` IN ('Group') AND `members`.`user_id` = 2 AND `members`.`type` IN ('GroupMember') AND `members`.`requested_at` IS NULL)))
UNION
SELECT `projects`.`id` FROM `projects` WHERE `projects`.`pending_delete` = 0 AND `projects`.`id` IN (SELECT DISTINCT `events`.`project_id` FROM `events` WHERE (`events`.`author_id` IS NOT NULL) AND (action = 5 OR (target_type in ('MergeRequest','Issue') AND action in (1,3,7))) AND `events`.`author_id` = 36 AND (created_at > '2015-08-01 06:29:42')) AND `projects`.`visibility_level` IN (20, 10))) AND (namespace_id != 41) ORDER BY `projects`.`id` DESC;
# Time: 160801 14:30:09
Implementation guide:
We could use the same solution as in !140194 (merged) to add pagination to this page. The controller-action is UsersController#contributed
Edited by Manoj M J [OOO, back on 22nd April]