Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Bug]: Missing database index for dav_shares resourceid, type and access #46015

Closed
4 of 8 tasks
ChristophWurst opened this issue Jun 20, 2024 · 4 comments · Fixed by #46036
Closed
4 of 8 tasks

[Bug]: Missing database index for dav_shares resourceid, type and access #46015

ChristophWurst opened this issue Jun 20, 2024 · 4 comments · Fixed by #46036
Assignees
Labels
3. to review Waiting for reviews 29-feedback bug feature: caldav Related to CalDAV internals feature: carddav Related to CardDAV internals feature: dav performance 🚀

Comments

@ChristophWurst
Copy link
Member

⚠️ This issue respects the following points: ⚠️

Bug description

The database query

SELECT
  `publicuri`
FROM
  `oc_dav_shares`
WHERE
  (`resourceid` = ?)
  AND (`access` = ?)

does not always use an index:

image

Query id is 03601b9aa9c7478bcc9506bd3b202407.

Steps to reproduce

  1. ???

Expected behavior

Efficient queries

Installation method

None

Nextcloud Server version

29

Operating system

None

PHP engine version

None

Web server

None

Database engine version

None

Is this bug present after an update or on a fresh install?

None

Are you using the Nextcloud Server Encryption module?

None

What user-backends are you using?

  • Default user-backend (database)
  • LDAP/ Active Directory
  • SSO - SAML
  • Other

Configuration report

No response

List of activated Apps

No response

Nextcloud Signing status

No response

Nextcloud Logs

No response

Additional info

No response

@ChristophWurst ChristophWurst added bug 1. to develop Accepted and waiting to be taken care of feature: dav performance 🚀 feature: caldav Related to CalDAV internals feature: carddav Related to CardDAV internals labels Jun 20, 2024
@ChristophWurst
Copy link
Member Author

Query 257a6846e9aa2f42d6276e58c803e1e3 has a similar problem:

SELECT
  `dav_shares`.`principaluri`,
  `dav_shares`.`resourceid`
FROM
  `oc_dav_shares` `dav_shares`
WHERE
  (`dav_shares`.`type` = ?)
  AND (`dav_shares`.`resourceid` = ?)

image

To improve both queries we will need two new indices:

  • (resourceid, access)
  • (resourceid, type)

@ChristophWurst ChristophWurst changed the title [Bug]: Missing database index for dav_shares resourceid and access [Bug]: Missing database index for dav_shares resourceid, type and access Jun 20, 2024
@SebastianKrupinski
Copy link
Contributor

@ChristophWurst should we do this automatically with a db migration? or is there another preferred method?

@ChristophWurst
Copy link
Member Author

It depends (tm). If we can assume that the table is not insanely big we can apply the index with a migration. If the table is big we should only adapt the existing migration that creates the table. This will add the index for new setups. For existing ones we can use the "missing index" events/listeners.

@SebastianKrupinski
Copy link
Contributor

Okay, I'll get working on it

@st3iny st3iny added 3. to review Waiting for reviews and removed 1. to develop Accepted and waiting to be taken care of labels Jun 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
3. to review Waiting for reviews 29-feedback bug feature: caldav Related to CalDAV internals feature: carddav Related to CardDAV internals feature: dav performance 🚀
Projects
Status: ☑️ Done
Development

Successfully merging a pull request may close this issue.

4 participants