diff options
-rw-r--r-- | lib/private/DB/QueryBuilder/QueryBuilder.php | 17 | ||||
-rw-r--r-- | lib/private/Files/Cache/Cache.php | 8 | ||||
-rw-r--r-- | lib/private/Files/Cache/CacheQueryBuilder.php | 19 |
3 files changed, 40 insertions, 4 deletions
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php index 352829a56ae..82add039cf5 100644 --- a/lib/private/DB/QueryBuilder/QueryBuilder.php +++ b/lib/private/DB/QueryBuilder/QueryBuilder.php @@ -1301,4 +1301,21 @@ class QueryBuilder implements IQueryBuilder { return $this->helper->quoteColumnName($alias); } + + /** + * Either appends to or replaces a single, generic query part. + * + * The available parts are: 'select', 'from', 'set', 'where', + * 'groupBy', 'having' and 'orderBy'. + * + * @param string $sqlPartName + * @param mixed $sqlPart + * @param bool $append + * + * @return $this This QueryBuilder instance. + */ + public function add(string $sqlPartName, $sqlPart, bool $append = false) { + $this->queryBuilder->add($sqlPartName, $sqlPart, $append); + return $this; + } } diff --git a/lib/private/Files/Cache/Cache.php b/lib/private/Files/Cache/Cache.php index 34ed6d33a2d..4e5edc0e0a6 100644 --- a/lib/private/Files/Cache/Cache.php +++ b/lib/private/Files/Cache/Cache.php @@ -848,7 +848,13 @@ class Cache implements ICache { protected function buildSearchQuery(ISearchQuery $searchQuery): IQueryBuilder { $builder = $this->getQueryBuilder(); - $query = $builder->selectFileCache('file'); + // mysql really likes to pick an index for sorting if it can't fully satisfy the where + // filter with an index, since search queries pretty much never are fully filtered by index + // mysql often picks an index for sorting instead of the *much* more useful index for filtering. + // + // To bypass this, we tell mysql explicitly not to use the mtime (the default order field) index, + // so it will instead pick an index that is actually useful. + $query = $builder->selectFileCache('file', 'ignore index for order by (fs_mtime)'); $query->whereStorageId(); diff --git a/lib/private/Files/Cache/CacheQueryBuilder.php b/lib/private/Files/Cache/CacheQueryBuilder.php index 2215cef9a15..9847375495e 100644 --- a/lib/private/Files/Cache/CacheQueryBuilder.php +++ b/lib/private/Files/Cache/CacheQueryBuilder.php @@ -26,6 +26,7 @@ declare(strict_types=1); namespace OC\Files\Cache; +use Doctrine\DBAL\Platforms\MySQLPlatform; use OC\DB\QueryBuilder\QueryBuilder; use OC\SystemConfig; use OCP\DB\QueryBuilder\IQueryBuilder; @@ -45,12 +46,24 @@ class CacheQueryBuilder extends QueryBuilder { $this->cache = $cache; } - public function selectFileCache(string $alias = null) { + public function selectFileCache(string $alias = null, string $mysqlIndexHint = '') { $name = $alias ? $alias : 'filecache'; $this->select("$name.fileid", 'storage', 'path', 'path_hash', "$name.parent", 'name', 'mimetype', 'mimepart', 'size', 'mtime', 'storage_mtime', 'encrypted', 'etag', 'permissions', 'checksum', 'metadata_etag', 'creation_time', 'upload_time') - ->from('filecache', $name) - ->leftJoin($name, 'filecache_extended', 'fe', $this->expr()->eq("$name.fileid", 'fe.fileid')); + ->from('filecache', $name); + if ($mysqlIndexHint !== '' && $this->getConnection()->getDatabasePlatform() instanceof MySQLPlatform) { + $this->add('join', [ + $this->quoteAlias($name) => [ + // horrible query builder crimes to sneak in raw sql after the "FROM oc_filecache $name" + 'joinType' => $mysqlIndexHint . ' left', + 'joinTable' => $this->getTableName('filecache_extended'), + 'joinAlias' => $this->quoteAlias('fe'), + 'joinCondition' => $this->expr()->eq("$name.fileid", 'fe.fileid'), + ], + ], true); + } else { + $this->leftJoin($name, 'filecache_extended', 'fe', $this->expr()->eq("$name.fileid", 'fe.fileid')); + } $this->alias = $name; |