{"action":"created","issue":{"url":"https://api.github.com/repos/owncloud/core/issues/10077","labels_url":"https://api.github.com/repos/owncloud/core/issues/10077/labels{/name}","comments_url":"https://api.github.com/repos/owncloud/core/issues/10077/comments","events_url":"https://api.github.com/repos/owncloud/core/issues/10077/events","html_url":"https://github.com/owncloud/core/issues/10077","id":39185068,"number":10077,"title":"shared cache search and searchByMime don't scale well","user":{"login":"butonic","id":956847,"avatar_url":"https://avatars.githubusercontent.com/u/956847?v=3","gravatar_id":"","url":"https://api.github.com/users/butonic","html_url":"https://github.com/butonic","followers_url":"https://api.github.com/users/butonic/followers","following_url":"https://api.github.com/users/butonic/following{/other_user}","gists_url":"https://api.github.com/users/butonic/gists{/gist_id}","starred_url":"https://api.github.com/users/butonic/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/butonic/subscriptions","organizations_url":"https://api.github.com/users/butonic/orgs","repos_url":"https://api.github.com/users/butonic/repos","events_url":"https://api.github.com/users/butonic/events{/privacy}","received_events_url":"https://api.github.com/users/butonic/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/owncloud/core/labels/blue-ticket","name":"blue-ticket","color":"0033CC"},{"url":"https://api.github.com/repos/owncloud/core/labels/enhancement","name":"enhancement","color":"02e10c"},{"url":"https://api.github.com/repos/owncloud/core/labels/performance","name":"performance","color":"fbca04"}],"state":"open","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/owncloud/core/milestones/48","html_url":"https://github.com/owncloud/core/milestones/9.0-current","labels_url":"https://api.github.com/repos/owncloud/core/milestones/48/labels","id":1196845,"number":48,"title":"9.0-current","description":"","creator":{"login":"cmonteroluque","id":879976,"avatar_url":"https://avatars.githubusercontent.com/u/879976?v=3","gravatar_id":"","url":"https://api.github.com/users/cmonteroluque","html_url":"https://github.com/cmonteroluque","followers_url":"https://api.github.com/users/cmonteroluque/followers","following_url":"https://api.github.com/users/cmonteroluque/following{/other_user}","gists_url":"https://api.github.com/users/cmonteroluque/gists{/gist_id}","starred_url":"https://api.github.com/users/cmonteroluque/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cmonteroluque/subscriptions","organizations_url":"https://api.github.com/users/cmonteroluque/orgs","repos_url":"https://api.github.com/users/cmonteroluque/repos","events_url":"https://api.github.com/users/cmonteroluque/events{/privacy}","received_events_url":"https://api.github.com/users/cmonteroluque/received_events","type":"User","site_admin":false},"open_issues":654,"closed_issues":1067,"state":"open","created_at":"2015-07-07T14:21:23Z","updated_at":"2016-01-22T15:20:27Z","due_on":"2016-03-08T04:00:00Z","closed_at":null},"comments":25,"created_at":"2014-07-31T11:20:26Z","updated_at":"2016-01-22T15:32:17Z","closed_at":null,"body":"The current implementation does not scale because `searchByMime()` as well as `search()` uses dozens of sql queries when walking the directory tree: getFolderContents first looks up the source cache via the shares table and that then looks up the files in the filecache. That is at least two queries per folder from what I can tell. Have a look at the following query log: http://pastebin.com/6V0M2NAg\r\n\r\nAnd I already stripped it of completely unneeded duplicate queries for user group and what not. But that is yet another issue.\r\n\r\nWhat I propose is having the current filecache and share tables and an additional relation table called 'oc_shares_files' having the fileid and the shareid as foreign keys:\r\n```SQL\r\nCREATE TABLE `oc_shares_files` (`shareid` int NOT NULL , `fileid` int NOT NULL , PRIMARY KEY (`shareid`, `fileid`));\r\n\r\n-- We can then join the share table via the relations to search files\r\nSELECT \"sf\".\"shareid\", \"fc\".\"fileid\"\r\nFROM \"oc_filecache\" \"fc\"\r\nJOIN \"oc_share_files\" \"sf\" ON \"sf\".\"fileid\"=\"fc\".\"fileid\"\r\nJOIN \"oc_share\" \"s\" ON \"sf\".\"shareid\"=\"s\".\"id\"\r\n WHERE \"fc\".\"name\" LIKE ?\r\nAND \"s\".\"share_with\" = ?\r\n```\r\npass in the pattern and the user searching through shared files. The query could be optimized to also show files owned by the user.\r\n\r\nMaintaining the relations is not that hard:\r\n```SQL\r\n-- add all children i nthe same storage when a new share is created: params are shareid, fileid of the shared file and path of the shared file with % appended,\r\nINSERT INTO  `oc_shares_files` (`shareid`,`fileid`)\r\n  SELECT ?, `fc`.`fileid` \r\n  FROM `oc_filecache` `fc` \r\n    JOIN `oc_filecache` `fc2` ON `fc`.`fileid` = `fc`.`fileid`\r\n  WHERE `fc`.`storage` = `fc2`.`storage`\r\n    AND `fc2`.`fileid` = ?\r\n    AND `fc`.`path` LIKE ?\r\n```\r\nAll this happens inside the database. A quick test on an oracle db that had to insert 7736 rows took less than a second. Honestly, this is what RDBMS are made for. On my productive mysql machine I did:\r\n```SQL\r\nSELECT count(*) FROM `oc_filecache` WHERE `path` LIKE 'files%';\r\n+----------+\r\n| count(*) |\r\n+----------+\r\n|    21750 |\r\n+----------+\r\n1 row in set (0.19 sec)\r\n\r\nINSERT INTO `oc_shares_files` (`shareid`,`fileid`) SELECT 1, `fc`.`fileid`    FROM `oc_filecache` `fc`      JOIN `oc_filecache` `fc2` ON `fc`.`fileid` = `fc`.`fileid`   WHERE `fc`.`storage` = `fc2`.`storage`     AND `fc2`.`fileid` = 2938     AND `fc`.`path` LIKE 'files%';\r\nQuery OK, 17261 rows affected (0.04 sec)\r\nRecords: 17261  Duplicates: 0  Warnings: 0\r\n```\r\nso ... IMHO performance is clearly not an issue here.\r\n\r\nOther operations can now also be offloaded to the database:\r\n```SQL\r\n-- on a create hook add the file to the shares_files relation by looking up the parent in the share_files table\r\nINSERT INTO `oc_shares_files` (`shareid`,`fileid`) SELECT `sf`.`shareid`, `fc`.`fileid` FROM `oc_filecache` `fc` JOIN `oc_share_files` `sf` ON `sf`.`fileid`=`fc`.`parent` WHERE `fc`.`fileid` = ?\r\n\r\n-- on a delete hook remove the file or all files of a share from the shares_files relation\r\n-- when deleting a file (or folder)\r\nDELETE FROM `oc_shares_files` WHERE `fileid` IN (SELECT `fileid` FROM `oc_filecache` WHERE `path` LIKE ? )\r\n-- when deleting a share\r\nDELETE FROM `oc_shares_files` WHERE `shareid` = ?\r\n```\r\n\r\nIf we were using foreign keys we could even use the cascade option to remove entries from the relation table.\r\n\r\nMigration to a relations table is easy because it only adds the relation table.\r\n\r\nIt is basically a speed tradeoff where we maintain the relations of the shared files in the database to speed up all queries related to file shares, because we can let the database handle permissions and don't have to go through php and the connection.\r\n\r\nOpen questions pointed out by @schiesbn:\r\nWhat happens when a different storage is mounted inside the shared folder?\r\nMy initial thought is that, since the whole storage is shared, it makes sens to have a `oc_shares_storages` table to track this case.\r\n\r\nLet me take a step back and think out loud here. Our `oc_filecache` should contain every file only once. The `oc_storages` is basically used to grant access to a subset of these files to users. The `oc_share` table actually does the same. The storages table also serves another purpose: naming the *physical* mount points in the *virtual* directory tree. By creating a share we effectively create a new storage that contains a subtree of the *virtual* directory tree. This subtree can contain any number of storages: *physical* mount poins as well as other *virtual* storages. ... mind blown ... Mathemathically speaking we are dealing with sets. Sets of existing files (storages) and sets of accessible files which is a subset of the filecache. The root for each accessible set is either the root of a storage or a share entry. The diffeerence is that with storages we have a way of joining or intersecting the set of files (because they share the same storage id) whereas with shares we cannot use joins or intersections, because they only contain the root element.\r\n\r\nAll of this gets even more interesting when a folder has been shared twice to the same user (via a->b, a->c, b->d, c->d) \r\n\r\nIt seems to me we are hiding access controls in storages and shares. Maybe we should make them explicit. I need to give this some more thought ... input is welcome, maybe from someone who is much more familiar with set theory than I am."},"comment":{"url":"https://api.github.com/repos/owncloud/core/issues/comments/173951836","html_url":"https://github.com/owncloud/core/issues/10077#issuecomment-173951836","issue_url":"https://api.github.com/repos/owncloud/core/issues/10077","id":173951836,"user":{"login":"felixboehm","id":1204907,"avatar_url":"https://avatars.githubusercontent.com/u/1204907?v=3","gravatar_id":"","url":"https://api.github.com/users/felixboehm","html_url":"https://github.com/felixboehm","followers_url":"https://api.github.com/users/felixboehm/followers","following_url":"https://api.github.com/users/felixboehm/following{/other_user}","gists_url":"https://api.github.com/users/felixboehm/gists{/gist_id}","starred_url":"https://api.github.com/users/felixboehm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/felixboehm/subscriptions","organizations_url":"https://api.github.com/users/felixboehm/orgs","repos_url":"https://api.github.com/users/felixboehm/repos","events_url":"https://api.github.com/users/felixboehm/events{/privacy}","received_events_url":"https://api.github.com/users/felixboehm/received_events","type":"User","site_admin":false},"created_at":"2016-01-22T15:32:17Z","updated_at":"2016-01-22T15:32:17Z","body":"No, there is no quick fix or workaround for now.\r\n"}}