Redundant database indices

[ChristophWurst]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Describe the bug

MariaDB [sys]> select * from sys.schema_redundant_indexes where table_name like 'oc_deck_%';
+--------------+------------------------+---------------------------+-------------------------+----------------------------+----------------------+---------------------------+---------------------------+----------------+-----------------------------------------------------------------------------------------+
| table_schema | table_name             | redundant_index_name      | redundant_index_columns | redundant_index_non_unique | dominant_index_name  | dominant_index_columns    | dominant_index_non_unique | subpart_exists | sql_drop_index                                                                          |
+--------------+------------------------+---------------------------+-------------------------+----------------------------+----------------------+---------------------------+---------------------------+----------------+-----------------------------------------------------------------------------------------+
| nextcloud    | oc_deck_assigned_users | deck_assigned_users_idx_c | card_id                 |                          1 | idx_card_participant | card_id,participant       |                         1 |              0 | ALTER TABLE `nextcloud`.`oc_deck_assigned_users` DROP INDEX `deck_assigned_users_idx_c` |
| nextcloud    | oc_deck_board_acl      | deck_board_acl_idx_i      | board_id                |                          1 | deck_board_acl_uq_i  | board_id,type,participant |                         0 |              0 | ALTER TABLE `nextcloud`.`oc_deck_board_acl` DROP INDEX `deck_board_acl_idx_i`           |
+--------------+------------------------+---------------------------+-------------------------+----------------------------+----------------------+---------------------------+---------------------------+----------------+-----------------------------------------------------------------------------------------+

create index deck_assigned_users_idx_c
    on nextclouddev.oc_deck_assigned_users (card_id);

create index idx_card_participant
    on nextclouddev.oc_deck_assigned_users (card_id, participant);

create index deck_board_acl_idx_i
    on nextclouddev.oc_deck_board_acl (board_id);

create unique index deck_board_acl_uq_i
    on nextclouddev.oc_deck_board_acl (board_id, type, participant);

The database can use idx_card_participant for queries with card_id and participant but also just with card_id. Hence maintaining deck_assigned_users_idx_c too is wasted database load. Same idea goes for deck_board_acl_uq_i and deck_board_acl_idx_i.

The additional maintenance is only a problem if the two tables have lots of INSERT and DELETEs or the indexed columns change value. Else this is not a big issue.

To Reproduce
Steps to reproduce the behavior:

1. Intall the app

Expected behavior

Efficient database index usage.

Screenshots
If applicable, add screenshots to help explain your problem.

Client details:

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version 26 prod/29 dev
• Device: [e.g. iPhone6, desktop]

Server details

Operating system:

Web server:

Database:

PHP version:

Nextcloud version: (see Nextcloud admin page)

Where did you install Nextcloud from:

Signing status:

Login as admin user into your Nextcloud and access
http://example.com/index.php/settings/integrity/failed
paste the results here.

List of activated apps:

If you have access to your command line run e.g.:
sudo -u www-data php occ app:list
from within your Nextcloud installation folder

Nextcloud configuration:

If you have access to your command line run e.g.:
sudo -u www-data php occ config:list system
from within your Nextcloud installation folder

or

Insert your config.php content here
Make sure to remove all sensitive content such as passwords. (e.g. database password, passwordsalt, secret, smtp password, …)

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

Logs

Nextcloud log (data/nextcloud.log)

Insert your Nextcloud log here

Browser log

Insert your browser log here, this could for example include:

a) The javascript console log
b) The network log
c) ...