diff options
author | blizzz <blizzz@arthur-schiwon.de> | 2022-05-17 00:36:57 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2022-05-17 00:36:57 +0300 |
commit | 4efd0cdadf3ad0b572ff729d3a1c1b3ebff6d74e (patch) | |
tree | a156b889d460ce8653bea41d32079f28c0eb56cb | |
parent | 762d1ff0507069b0d3f9d5535f33d324cbbb7a8a (diff) | |
parent | 32cd1bed38104548ed12cb3cedf0f82db0b6502f (diff) |
Merge pull request #32123 from nextcloud/backport/32074/stable24
[stable24] tell mysql to ignore the sort index for search queries
-rw-r--r-- | lib/private/DB/QueryBuilder/QueryBuilder.php | 2 | ||||
-rw-r--r-- | lib/private/Files/Cache/SearchBuilder.php | 11 | ||||
-rw-r--r-- | lib/public/DB/QueryBuilder/IQueryBuilder.php | 2 |
3 files changed, 13 insertions, 2 deletions
diff --git a/lib/private/DB/QueryBuilder/QueryBuilder.php b/lib/private/DB/QueryBuilder/QueryBuilder.php index 4ecfd773492..fc436383b04 100644 --- a/lib/private/DB/QueryBuilder/QueryBuilder.php +++ b/lib/private/DB/QueryBuilder/QueryBuilder.php @@ -1113,7 +1113,7 @@ class QueryBuilder implements IQueryBuilder { /** * Adds an ordering to the query results. * - * @param string $sort The ordering expression. + * @param string|ILiteral|IParameter|IQueryFunction $sort The ordering expression. * @param string $order The ordering direction. * * @return $this This QueryBuilder instance. diff --git a/lib/private/Files/Cache/SearchBuilder.php b/lib/private/Files/Cache/SearchBuilder.php index 003d3ac15e7..c8c442bcb8c 100644 --- a/lib/private/Files/Cache/SearchBuilder.php +++ b/lib/private/Files/Cache/SearchBuilder.php @@ -232,6 +232,17 @@ class SearchBuilder { if ($field === 'fileid') { $field = 'file.fileid'; } + + // 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. + // + // By changing the order by to an expression, mysql isn't smart enough to see that it could still + // use the index, so it instead picks an index for the filtering + if ($field === 'mtime') { + $field = $query->func()->add($field, $query->createNamedParameter(0)); + } + $query->addOrderBy($field, $order->getDirection()); } } diff --git a/lib/public/DB/QueryBuilder/IQueryBuilder.php b/lib/public/DB/QueryBuilder/IQueryBuilder.php index afca9e372ee..e3257e82bca 100644 --- a/lib/public/DB/QueryBuilder/IQueryBuilder.php +++ b/lib/public/DB/QueryBuilder/IQueryBuilder.php @@ -834,7 +834,7 @@ interface IQueryBuilder { /** * Adds an ordering to the query results. * - * @param string $sort The ordering expression. + * @param string|ILiteral|IParameter|IQueryFunction $sort The ordering expression. * @param string $order The ordering direction. * * @return $this This QueryBuilder instance. |