{"action":"created","issue":{"url":"https://api.github.com/repos/owncloud/core/issues/21670","labels_url":"https://api.github.com/repos/owncloud/core/issues/21670/labels{/name}","comments_url":"https://api.github.com/repos/owncloud/core/issues/21670/comments","events_url":"https://api.github.com/repos/owncloud/core/issues/21670/events","html_url":"https://github.com/owncloud/core/issues/21670","id":126256972,"number":21670,"title":"Slow database queries -- Best details on optimizing","user":{"login":"gig13","id":4803385,"avatar_url":"https://avatars.githubusercontent.com/u/4803385?v=3","gravatar_id":"","url":"https://api.github.com/users/gig13","html_url":"https://github.com/gig13","followers_url":"https://api.github.com/users/gig13/followers","following_url":"https://api.github.com/users/gig13/following{/other_user}","gists_url":"https://api.github.com/users/gig13/gists{/gist_id}","starred_url":"https://api.github.com/users/gig13/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gig13/subscriptions","organizations_url":"https://api.github.com/users/gig13/orgs","repos_url":"https://api.github.com/users/gig13/repos","events_url":"https://api.github.com/users/gig13/events{/privacy}","received_events_url":"https://api.github.com/users/gig13/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/owncloud/core/labels/blue-ticket","name":"blue-ticket","color":"0033CC"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2016-01-12T19:32:49Z","updated_at":"2016-01-12T20:52:01Z","closed_at":null,"body":"### Expected behaviour\r\nQueries should not take an excessive amount of time.\r\n\r\n### Actual behaviour\r\nAfter some research we found a big latency when a query is done:\r\n```\r\nMariaDB [itcs_ab00b5]> SELECT `path`, `fileid`, storage FROM `oc_filecache`\r\nWHERE `storage` = '15' AND `path` LIKE\r\n'files\\\\_encryption/keys/files/DASHBOARD/2015/%' group by storage;\r\n+---------------------------------------------------------------------------\r\n-------------------------------------------------------------------+--------\r\n-+---------+\r\n| path\r\n| fileid | storage |\r\n+---------------------------------------------------------------------------\r\n-------------------------------------------------------------------+--------\r\n-+---------+\r\n| files_encryption/keys/files/DASHBOARD/2015/150109/dashboard cytokines et\r\nELISA 2015JA09.xlsx/OC_DEFAULT_MODULE/name@somewhere.com.shareKey |\r\n2076359 | 15 |\r\n+---------------------------------------------------------------------------\r\n-------------------------------------------------------------------+--------\r\n-+---------+\r\n1 row in set (1 min 39.73 sec)\r\n```\r\n\r\nAlso generally slow on oc_filecache\r\n```\r\nMariaDB [itcs_ab00b5]> SELECT count(*), `path`, `fileid`, storage\r\nFROM `oc_filecache` WHERE `storage` = '15';\r\n+----------+----------------------------------------------------------------\r\n---------------------------------------------------+--------+---------+\r\n| count(*) | path\r\n| fileid | storage |\r\n+----------+----------------------------------------------------------------\r\n---------------------------------------------------+--------+---------+\r\n| 505276 |\r\nencryption_migration_backup_2015-08-06_02-03-50/keyfiles/ASSURANCE\r\nQUALITE/CONTROLE QUALITÉ/Dossiers Analytiques | 891024 | 15 |\r\n+----------+----------------------------------------------------------------\r\n---------------------------------------------------+--------+---------+\r\n1 row in set (1 min 38.31 sec)\r\n```\r\n\r\n\r\nIs there a way to optimize the oc_filecache table?\r\n\r\n### Server configuration\r\n**Operating system**: Debian 7.7\r\n\r\n**Web server:** nginx 1.6.2\r\n\r\n**Database:** Mariadb 10.0 in a passive/active failover mode galera cluster. \r\n\r\n**PHP version:** 5.4.35\r\n\r\n**ownCloud version:** 8.2.1 Server Edition\r\n\r\n**Updated from an older ownCloud or fresh install:** Updated\r\n\r\n**Are you using encryption:** YES\r\n\r\n**App list**\r\n\r\nEnabled:\r\n- activity: 2.1.3\r\n- client_api: 0.1\r\n- encryption: 1.1.0\r\n- files: 1.2.0\r\n- files_pdfviewer: 0.7\r\n- files_sharing: 0.7.0\r\n- files_texteditor: 2.0\r\n- files_trashbin: 0.7.0\r\n- files_versions: 1.1.0\r\n- files_videoviewer: 0.1.3\r\n- firstrunwizard: 1.1\r\n- galleryplus: 14.3.0\r\n- search_lucene: 0.7.0\r\n\r\nDisabled:\r\n- calendar\r\n- contacts\r\n- external\r\n- files_external\r\n- notifications\r\n- provisioning_api\r\n- templateeditor\r\n- updater\r\n- user_external\r\n- user_ldap \r\n\r\n\r\n\r\n@MorrisJobke \r\n\r\n"},"comment":{"url":"https://api.github.com/repos/owncloud/core/issues/comments/171050834","html_url":"https://github.com/owncloud/core/issues/21670#issuecomment-171050834","issue_url":"https://api.github.com/repos/owncloud/core/issues/21670","id":171050834,"user":{"login":"rullzer","id":45821,"avatar_url":"https://avatars.githubusercontent.com/u/45821?v=3","gravatar_id":"","url":"https://api.github.com/users/rullzer","html_url":"https://github.com/rullzer","followers_url":"https://api.github.com/users/rullzer/followers","following_url":"https://api.github.com/users/rullzer/following{/other_user}","gists_url":"https://api.github.com/users/rullzer/gists{/gist_id}","starred_url":"https://api.github.com/users/rullzer/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rullzer/subscriptions","organizations_url":"https://api.github.com/users/rullzer/orgs","repos_url":"https://api.github.com/users/rullzer/repos","events_url":"https://api.github.com/users/rullzer/events{/privacy}","received_events_url":"https://api.github.com/users/rullzer/received_events","type":"User","site_admin":false},"created_at":"2016-01-12T20:52:01Z","updated_at":"2016-01-12T20:52:01Z","body":"Ah like queries... the root of all evil ;)\r\n\r\nRegarding the index. I don't think our current DB schema does not allow to create indexes of length I believe. \r\n\r\n@DeepDiver1975 another case once we switch fully to doctrine"}}