Drop and recreation of database indices on upgrade

[ChristophWurst]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration/webserver/proxy issue.
• This issue is not already reported on Github (I've searched it).
• I agree to follow Nextcloud's Code of Conduct.

What went wrong, what did you observe?

Upgrades are heavy on the database due to

polls/appinfo/info.xml

Lines 61 to 68 in 7e41c26

	<step>OCA\Polls\Migration\RepairSteps\RemoveIndices</step>
	</pre-migration>
	<post-migration>
	<step>OCA\Polls\Migration\RepairSteps\DropOrphanedTables</step>
	<step>OCA\Polls\Migration\RepairSteps\DropOrphanedColumns</step>
	<step>OCA\Polls\Migration\RepairSteps\UpdateHashes</step>
	<step>OCA\Polls\Migration\RepairSteps\DeleteInvalidRecords</step>
	<step>OCA\Polls\Migration\RepairSteps\CreateIndices</step>

. Dropping an index is cheap. Recreating it is expensive.

What did you expect, how polls should behave instead?

Use migration files or the optional indices APIs to add indices. Add them only once. Only drop them when they are not needed anymore.

What steps does it need to replay this bug?

1. Install an older version of the app
2. Upgrade

Installation method

Installed/updated from the appstore (Apps section of your site)

Installation type

Updated from a minor version within same major version (i.e. 4.0.0 to 4.1.1)

Affected polls version

v7.2.1

Which browser did you use, when experiencing the bug?

• Firefox
• Chrome
• Chromium/Chromium based (i.e. Edge)
• Safari
• Other/Don't know

Other browser

No response

Add your browser log here

No response

Additional client environment information

No response

NC version

Nextcloud 27

Other Nextcloud version

No response

PHP engine version

PHP 8.1

Other PHP version

No response

Database engine

MySQL

Database Engine version or other Database

No response

Which user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other/Don't know

Add your nextcloud server log here

No response

Additional environment informations

No response

Configuration report

No response

List of activated Apps

No response

Nextcloud Signing status

No response

Additional Information

No response

[dartcafe]

Since the update may change the database shema, it is necessary to remove the indices, because of the constraints.

And since the update is done rarely I would say it is a acceptable cost.

[sorbaugh]

Since the update may change the database shema, it is necessary to remove the indices, because of the constraints.

And since the update is done rarely I would say it is a acceptable cost.

Hello @dartcafe ,

to give some context, a very large instance that uses the Polls App had a huge downtime when upgrading. Analyzing the problem we discovered that it is related to this indices situation.

The ideal way Nextcloud application manages the database schema is the following:

• For each version where the schema changes, create a Migration file to apply the changes to the schema. You can create tables, columns and indices in there.
• If you modify a table in a way that invalidates an index, then delete the index if present in the matching Migration file.

You should not have to worry about "the update may change the database schema", you know if the update changes the schema or not because that depends on the Migration classes you added.

To ensure that Polls remains compatible with very large instances, we propose to write the PR to replace the RepairSteps in place in polls app with standard migration classes, would you be open to review and merge it?

[dartcafe]

I know of the context. But:

1. 'huge' is relative
2. Updates should be done in planned maintenance times, so performance should not be a critical issue of updates.
3. Polls is not part of the enterprize plan. Although I am open for offers. 😉

Don't get me wrong, I totally understand the request.

But there was an intention behind the current way doing the migration. And I was totally aware of the differences to the recommended way, which I decied to go away from. I t just made my work easier. And it had never been an issue for years.

Changing back to the recommended way is possible, but has side effects and needs maintenance. So please give me time to think about it. Maybe the preconditions for a vue3 beta and possibly release will be done until that.

[dartcafe]

I forgot, since it is not a bug by definition: It is always helpful to understand and maybe for reproduction, if details of the environment are delivered.

This way, it is possible to understand the value, which the change could develop.

What I mean, what time got measured, how many db entries are responsible for the issue, etc.

[AndyScherzinger]

So in short while still investigating and not yet having found the root cause.

• The update on a large in stage runs for more than an hour because this and it is done in any case, so any update recreating indices seems overkill
• now with an update from 7.1.4 to 7.2.1 despite no changes to the backend for the calls when opening polls it triggers a query to the DB running 22 minutes when just a single user has access and no further queries hit the database at that time. Multiply this with a decent amount of user and your system is down. This is a slightly different matter. Yet with an ever changing DB structure due to recreations also a tricky way to trying to ensure everything stays consistent. Miss an index for recreation? Kaboom.

Furthermore we started working on first steps towards zero downtime deployments at least for some scenarios so not doing DB changes when not necessary would be one important aspect.
Not say the later is a reason to change things. Just equally putting out arguments why there are also good reasons not to recreate indexes as a safety measure.

[AndyScherzinger]

The number of records I can see if I can get these values somehow and post them here.

@ChristophWurst might remember the number of records maybe.

[dartcafe]

Thanks for the first insight. 22 minutes is indeed huge. Are you able to identify any particular long running index creation?

I ask because if one index or a kind of index is/are responsible for the run time, maybe some further action has to be done, to also avoid long running index recreation times even if the index creation is neccessary for a single migration.

I suspect the foreign key constraints are the time killer, since the other indices are mostly simple ones.

Am I right, if I guess it is MariaDB/MySQL?

[AndyScherzinger]

MySQL cluster with 3 nodes in total, so the index also needs to be replicated in the end. If I remember correctly with proxysql between web and db tier.
The later should matter for the DB changes but just for the later operational scenario managing connections

[AndyScherzinger]

So this is the query running for 22 minutes when run through an explain

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)

Which is the query being triggered when opening the frontend

[dartcafe]

Can you add the row count of the polls_* tables?

[dartcafe]

Is ist true, that there are 7566 polls in the database?

[AndyScherzinger]

Is ist true, that there are 7566 polls in the database?

@dartcafe Yes, that is true, see rough numbers below:

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

[dartcafe]

😮 Oooookay. N..nice.
But since it is a different issue this should be treated in another issue.
#3688

[dartcafe]

I would like to come back to the index creation. Can you tell the time consuming index creation?

[AndyScherzinger]

I don't know exactly but the DB has been under heavy load during the upgrade (with system in maintenance mode and cut of from client requests via network) for an hour. That is all I can tell at the moment.

[come-nc]

As a middle ground proposition or a first step, how about #3689 ?

[AndyScherzinger]

Further reasoning why not replacing indexes every time without an actual need for it in terms of "no structural changes, no need to recreate indexes"

On the large instance if you ran the following SQL command before an upgrade (in this case 7.1.4) and more than 1,5h after the upgrade (to 7.2.1)

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`;

Before the upgrade

+------+--------------------+-------------------+--------+--------------------------------------------------------------+----------------------+---------+---------------------------------+------+----------------------------------------------+
| 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                            | 7823 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY            | options           | ref    | UNIQ_options,IDX_9AA5663C947C0F                              | IDX_9AA5663C947C0F   | 8       | nextcloud.polls_polls.id        | 3    | 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        | 4    | Using where                                  |
|    4 | DEPENDENT SUBQUERY | user_vote_sub     | ref    | UNIQ_votes,IDX_A20806F93C947C0F                              | UNIQ_votes           | 1034    | func,const                      | 2    | Using index condition                        |
|    4 | DEPENDENT SUBQUERY | vote_options_join | ref    | UNIQ_options,IDX_9AA5663C947C0F                              | IDX_9AA5663C947C0F   | 8       | nextcloud.user_vote_sub.poll_id | 3    | Using where; Not exists                      |
|    3 | DEPENDENT SUBQUERY | user_vote_sub     | ref    | UNIQ_votes,IDX_A20806F93C947C0F                              | UNIQ_votes           | 1034    | func,const                      | 2    | Using index condition; Using where           |
|    2 | DEPENDENT SUBQUERY | user_vote_sub     | ref    | UNIQ_votes,IDX_A20806F93C947C0F                              | UNIQ_votes           | 1034    | func,const                      | 2    | Using index condition                        |
+------+--------------------+-------------------+--------+--------------------------------------------------------------+----------------------+---------+---------------------------------+------+----------------------------------------------+
8 rows in set (0.019 sec)

After the upgrade

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` = 'crieger')) 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` = 'crieger') 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` = 'crieger') 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` = 'crieger') 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` <> 'crieger' 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)

As you can see, even after such a long time the indexes present and recreated aren't picked up by the DB because it lags the relevant index stats (say goodby to them when you drop an index).

The query when run with an index: 1.8seconds, without hitting all the indexes 22 minutes

So while #3688 is one fix or mitigation in terms of improving performance, not recreating indexes when there is no need to via #3689 is equally valid and I would propose to do both. Take the above case, it by now is so bad, that I do not expect the system to ever come back to life, because it takes the DB down, so how should the DB improve queries or their respective execution plan if you just took down the Nextcloud system in front of the DB since the DB peeked out at 100% utilization.

So to finish my "speech" and rest my case: I vote for doing both yet as a first quick fix, make the index recreation smarter (still needs some fixing) and ship that to unblock upgrades, optimize the queries in general in a second step. That one is potentially more complex since @come-nc mentioned that slitting the queries up might not bring significant performance improvements. So ultimately as seen above the main performance boost is to ensure queries hit indexes in order to be fast (I know, I am stating the obvious things, but just wanted to make it explicit).

[AndyScherzinger]

As seen above after recreating the indexes, even after 1,5h the query still misses to hit 3 indexes. Lacking the index stats the DB believes the query will be faster not using the index which is proven wrong, but the DB doesn't know (yet).

[AndyScherzinger]

Christoph's or our interpretation: same query, same indices available, but the DB doesn't use UNIQ_votes. That means three of the query parts run without an index after the upgrade, while they ran with an index before.

MariaDB [nextcloud]> show index from oc_polls_votes;
+----------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| oc_polls_votes | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| oc_polls_votes | 0 | UNIQ_votes | 1 | poll_id | A | 0 | NULL | NULL | | BTREE | | |
| oc_polls_votes | 0 | UNIQ_votes | 2 | user_id | A | 0 | NULL | NULL | | BTREE | | |
| oc_polls_votes | 0 | UNIQ_votes | 3 | vote_option_hash | A | 0 | NULL | NULL | YES | BTREE | | |
| oc_polls_votes | 1 | IDX_A20806F93C947C0F | 1 | poll_id | A | 0 | NULL | NULL | | BTREE | | |
+----------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.003 sec)

^ that was after the upgrade. Cardinality=0. Research suggested that this means the db doesn't have accurate statistics about the index. Thus the db might wrongly assume it's better to execute the query without the index.

[AndyScherzinger]

For the sake of completeness, stats from prod

MariaDB [(none)]> show variables like 'innodb_stats%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_include_delete_marked   | OFF         |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_modified_counter        | 0           |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_traditional             | ON          |
| innodb_stats_transient_sample_pages  | 8           |
+--------------------------------------+-------------+

[AndyScherzinger]

Happy to discuss all the infos above either here or at https://cloud.nextcloud.com/call/68qjowkv (the polls chat)