Index of acl rules might not be used properly
Open
Nobody has claimed this yet.
1. to develop
bug
performance 🚀
- Dominant language
- JavaScript
- Stars
- 1.4k
- Forks
- 354
- Avg merge
- 1d 10h
- Merged PRs (30d)
- 43
Description
MariaDB [nextcloud]> explain SELECT DISTINCT b.id FROM oc_deck_boards b LEFT JOIN oc_deck_board_acl acl ON b.id = acl.board_id WHERE (owner = 'admin') OR ((acl.participant = 'admin') AND (acl.type = 0)) OR ((acl.participant IN ('group', 'group2')) AND (acl.type = 1));
+------+-------------+-------+-------+------------------------------------------+---------------------+---------+----------------+------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+------------------------------------------+---------------------+---------+----------------+------+------------------------------------+
| 1 | SIMPLE | b | index | idx_owner_modified | idx_owner_modified | 263 | NULL | 1 | Using index; Using temporary |
| 1 | SIMPLE | acl | ref | deck_board_acl_uq_i,deck_board_acl_idx_i | deck_board_acl_uq_i | 8 | nextcloud.b.id | 1 | Using where; Using index; Distinct |
+------+-------------+-------+-------+------------------------------------------+---------------------+---------+----------------+------+------------------------------------+
2 rows in set (0.003 sec)
MariaDB [nextcloud]> show create table oc_deck_board_acl;
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| oc_deck_board_acl | CREATE TABLE `oc_deck_board_acl` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`board_id` bigint(20) NOT NULL,
`type` int(11) NOT NULL,
`participant` varchar(64) NOT NULL,
`permission_edit` tinyint(1) DEFAULT 0,
`permission_share` tinyint(1) DEFAULT 0,
`permission_manage` tinyint(1) DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `deck_board_acl_uq_i` (`board_id`,`type`,`participant`),
KEY `deck_board_acl_idx_i` (`board_id`),
KEY `idx_participant_type` (`participant`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start by reproducing the reported EXPLAIN query against oc_deck_boards and oc_deck_board_acl, then inspect the CREATE TABLE output and the listed indexes, especially idx_participant_type. Compare the optimizer's chosen index with the ACL predicates and determine what behavior should be considered correct; done means the query uses an appropriate index or the concern is documented as expected behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mariadb
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 30/100