Missed sql index

[Temtaime]

Table filecache has no index on the path column.
Slow query log sometimes shows that queries like

SELECT path, fileid FROM oc_filecache WHERE storage = '1' AND path LIKE 'files/projects%'

Take > 10 secs.
Adding an index on the 'path' column seems to help.

[LukasReschke]

@icewind1991 @nickvergessen

[dirkpape]

I had the same problem. In mysql-slow.log I saw queries like this lasting more than 40s when renaming a folder or sharing a folder when encryption was enabled.

# Query_time: 44.722292  Lock_time: 0.000035 Rows_sent: 13  Rows_examined: 648331
SET timestamp=1494139934;
SELECT `path`, `fileid` FROM `oc_filecache` WHERE `storage` = '4' AND `path` LIKE 'files\\_encryption/keys/files/<..path..cut>/%';

I created the index on the path column for oc_filecache and the problem was gone.
Thanks,
Dirk

[nickvergessen]

Well makes sense, the main problem is that adding it to huge instances will take like forever.

[icewind1991]

The problem is that not all mysql configurations allow creating index on long string columns meaning that we can't rely on indexes on this column.

Ongoing work is being done to reduce the number of queries that run into this problem

[MorrisJobke]

We improved in this regard hugely. Please check out the latest version and check the admin page if there are pending indexes (it will show it to you and also list a command on how to add it).

Beside that we should focus on #10926