Skip to content

The circles membership query always causes on-disk temp tables, an alternate way of sorting would be helpful #3155

Open

Description

@banane4711

How to use GitHub

  • Please use the 👍 reaction to show that you are interested into the same feature.
  • 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.

Feature request

Which Nextcloud Version are you currently using: (see administration page)
I am working on Nextcloud version 35.0.2 (Circles 35.0.0) running on a Debian 13 server with MariaDB 11.8.6.

Is your feature request related to a problem? Please describe.
The max_heap_table_size and tmp_table_size are set to 96M already and not used up, but still tmp tables are created on disk.
I did some tries as the finding was mentioned in the nextcloud admin page and it showed a warning on the Nextcloud admin page, that the database could be slow.
During a trace run of around 15 hours, 2,446 disk tmp tables have been created, more than half of them by circles (1,258). The query finally resulted only 2 rows. It seems sorting the b.creation column causes MariaDB to work with temp tables containing the longtext columns (description, settings, inheritance_path) on the disk. The query used is:

SELECT b.unique_id, b.name, b.display_name, b.sanitized_name, b.source, b.description, b.settings, b.config, b.contact_addressbook, b.contact_groupname, b.creation, b_j.single_id, b_j.circle_id, b_j.level, b_j.inheritance_first, b_j.inheritance_last, b_j.inheritance_path, b_j.inheritance_depth FROM oc_circles_circle b INNER JOIN oc_circles_membership b_j ON b_j.circle_id = b.unique_id WHERE ((b.config & 1) = 0) AND (b_j.single_id = '<single_id>') ORDER BY b.creation ASC

with the EXPLAIN of:

oc_circles_membership: primary key, 2 rows; oc_circles_circle: unique key — Using where; Using temporary; Using filesort

Describe the solution you'd like
It would be nice to improve circles in a way not to use slow disk temp tables e.g. by sorting after the query in PHP

Describe alternatives you've considered
I tried to raise the tmp_table_size, but this shows no effect for this query, the TEXT/BLOB was always on the disk.
MariaDB finds the rows of both tables through an index („primary key“, „unique key“), and has to sort it afterwards. A temporary table ("Using temporary") is built and as that table holds the longtext columns, it goes to disk.

Additional context
The nextcloud/serverinfo#1224 is a similar topic

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions