Optimize polls list loading time

[dartcafe]

⚠️ This issue respects the following points: ⚠️

• [X] I agree to follow Nextcloud's Code of Conduct.

Describe the goal you'd like to achieve

As a poll user, I want to **enter the app without long loading times.

Describe possible solutions

No response

Additional context

There is an instance, where collecting all polls lasts verly long (something about 22 minutes).

It seems that this SQL is responsible for the huge loading time:

MySQL [nextcloud] > EXPLAIN
SELECT `polls_polls`.*,
    (
        SELECT COUNT(`user_vote_sub`.`vote_answer`)
        FROM `oc_polls_votes` `user_vote_sub`
        WHERE (`user_vote_sub`.`poll_id` = `polls_polls`.`id`)
            AND (`user_vote_sub`.`user_id` = 'r-e-m-o-v-e-d')
    ) AS `current_user_count_votes`,
    (
        SELECT COUNT(`user_vote_sub`.`vote_answer`)
        FROM `oc_polls_votes` `user_vote_sub`
        WHERE (`user_vote_sub`.`poll_id` = `polls_polls`.`id`)
            AND (`user_vote_sub`.`user_id` = 'r-e-m-o-v-e-d')
            AND (`user_vote_sub`.`vote_answer` = 'yes')
    ) AS `current_user_count_votes_yes`,
    (
        SELECT COUNT(`user_vote_sub`.`vote_answer`)
        FROM `oc_polls_votes` `user_vote_sub`
            LEFT JOIN `oc_polls_options` `vote_options_join` ON (
                `vote_options_join`.`poll_id` = `user_vote_sub`.`poll_id`
            )
            AND (
                `vote_options_join`.`poll_option_text` = `user_vote_sub`.`vote_option_text`
            )
            AND (`vote_options_join`.`deleted` = '0')
        WHERE (`user_vote_sub`.`poll_id` = `polls_polls`.`id`)
            AND (`user_vote_sub`.`user_id` = 'r-e-m-o-v-e-d')
            AND (`vote_options_join`.`id` IS NULL)
    ) AS `current_user_count_orphaned_votes`,
    coalesce(MAX(options.timestamp), 0) AS max_date,
    coalesce(MIN(options.timestamp), 1724870381) AS min_date,
    COUNT(`options`.`id`) AS `count_options`,
    coalesce(user_shares.type, '') AS user_role,
    `user_shares`.`locked` AS `is_current_user_locked`,
    coalesce(user_shares.token, '') AS share_token,
    group_concat(distinct group_shares.user_id SEPARATOR ',') AS group_shares
FROM `oc_polls_polls` `polls_polls`
    LEFT JOIN `oc_polls_options` `options` ON (`polls_polls`.`id` = `options`.`poll_id`)
    AND (`options`.`deleted` = '0')
    LEFT JOIN `oc_polls_share` `user_shares` ON (`polls_polls`.`id` = `user_shares`.`poll_id`)
    AND (`user_shares`.`user_id` = 'r-e-m-o-v-e-d')
    AND (`user_shares`.`deleted` = '0')
    LEFT JOIN `oc_polls_share` `group_shares` ON (`polls_polls`.`id` = `group_shares`.`poll_id`)
    AND (`group_shares`.`type` = 'group')
    AND (`group_shares`.`deleted` = '0')
WHERE `polls_polls`.`owner` <> 'r-e-m-o-v-e-d'
GROUP BY `polls_polls`.`id`,
    `user_shares`.`type`,
    `user_shares`.`locked`,
    `user_shares`.`token`;

+------+--------------------+-------------------+--------+--------------------------------------------------------------+----------------------+---------+---------------------------------+------+----------------------------------------------+
| id   | select_type        | table             | type   | possible_keys                                                | key                  | key_len | ref                             | rows | Extra                                        |
+------+--------------------+-------------------+--------+--------------------------------------------------------------+----------------------+---------+---------------------------------+------+----------------------------------------------+
|    1 | PRIMARY            | polls_polls       | ALL    | polls_polls_owner_deleted_idx,polls_polls_owners_non_deleted | NULL                 | NULL    | NULL                            | 7566 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY            | options           | ref    | UNIQ_options,IDX_9AA5663C947C0F                              | IDX_9AA5663C947C0F   | 8       | nextcloud.polls_polls.id        | 1    | Using where                                  |
|    1 | PRIMARY            | user_shares       | eq_ref | UNIQ_shares,IDX_1C85E16C3C947C0F                             | UNIQ_shares          | 1034    | nextcloud.polls_polls.id,const  | 1    | Using where                                  |
|    1 | PRIMARY            | group_shares      | ref    | UNIQ_shares,IDX_1C85E16C3C947C0F                             | IDX_1C85E16C3C947C0F | 8       | nextcloud.polls_polls.id        | 5    | Using where                                  |
|    4 | DEPENDENT SUBQUERY | user_vote_sub     | ALL    | UNIQ_votes,IDX_A20806F93C947C0F                              | NULL                 | NULL    | NULL                            | 1    | Using where                                  |
|    4 | DEPENDENT SUBQUERY | vote_options_join | ref    | UNIQ_options,IDX_9AA5663C947C0F                              | IDX_9AA5663C947C0F   | 8       | nextcloud.user_vote_sub.poll_id | 1    | Using where; Not exists                      |
|    3 | DEPENDENT SUBQUERY | user_vote_sub     | ALL    | UNIQ_votes,IDX_A20806F93C947C0F                              | NULL                 | NULL    | NULL                            | 1    | Using where                                  |
|    2 | DEPENDENT SUBQUERY | user_vote_sub     | ALL    | UNIQ_votes,IDX_A20806F93C947C0F                              | NULL                 | NULL    | NULL                            | 1    | Using where                                  |
+------+--------------------+-------------------+--------+--------------------------------------------------------------+----------------------+---------+---------------------------------+------+----------------------------------------------+
8 rows in set (0.004 sec)

[dartcafe]

Some numbers see https://github.com/nextcloud/polls/issues/3679#issuecomment-2326166328:

• 7.5k polls
• 200k votes
• 60k shares
• 40k options

[dartcafe]

@AndyScherzinger I think, the subqueries cause a heavy load, if any poll scans shares, ... every poll.

Additionally some indices should be created to avoid full table scans. It would be nice if someone could test the changes I will try in a PR by applying the patches. Do you think you can do that?

[AndyScherzinger]

I can ask people to test tomorrow and will post an analysis later why the index recreation is expected to cause the problem in the first place (sorry for the teaser, currently on the phone)

[AndyScherzinger]

Back at the PC, posted my comment as mentioned in https://github.com/nextcloud/polls/issues/3679#issuecomment-2329541569 since it is about the reasons why indexes should not be recreated except when actually needed to.

[AndyScherzinger]

But also to comment on the statement @dartcafe

I think, the subqueries cause a heavy load, if any poll scans shares, ... every poll.

https://github.com/nextcloud/polls/issues/3679#issuecomment-2329541569 proves this hypothesis but also shows why recreating indexes also makes you not hit an index and if that takes down the system the optimized queries using indexes won't solve the issue. They are just another optimization step, also helpful but not the cause of the issue.

[dartcafe]

Index creation is another issue. But I count the priority of the runtime issue higher. So lets first solve this first.

I still have limited time (and brain) theese weeks.

[AndyScherzinger]

Index creation is another issue. But I count the priority of the runtime issue higher. So lets first solve this first.

I disagree. The index issue blocks upgrade large scale instances completely, for the app as well as for the server (which triggers updating apps as part of the server upgrade automatically).

I still have limited time (and brain) theese weeks.

Like I said in the chat, I totally get that and am in the same position. Yet I am in the same situation for exactly this reason - I need a Polls app usable for a large scale instance meaning the ability to update without taking down the system indefinitely - which is why I disagree with the assessment in the first comment of this post.

---

So how can we move forward so this works for all of us and meets everyone's requirements and is not an issue for either one of us?