Merge pull request #16419 from owncloud/user-group-backend-performance

Reduce the complexity of the search queries in the user/group backends to a minimum

2 files changed, 47 insertions, 11 deletions

diff --git a/lib/private/group/database.php b/lib/private/group/database.php
index a58d66010d5..ad6174808bb 100644
--- a/lib/private/group/database.php
+++ b/lib/private/group/database.php
@@ -180,8 +180,15 @@ class OC_Group_Database extends OC_Group_Backend {
 	 * Returns a list with all groups
 	 */
 	public function getGroups($search = '', $limit = null, $offset = null) {
-		$stmt = OC_DB::prepare('SELECT `gid` FROM `*PREFIX*groups` WHERE LOWER(`gid`) LIKE LOWER(?) ORDER BY `gid` ASC', $limit, $offset);
-		$result = $stmt->execute(array('%' . $search . '%'));
+		$parameters = [];
+		$searchLike = '';
+		if ($search !== '') {
+			$parameters[] = '%' . $search . '%';
+			$searchLike = ' WHERE LOWER(`gid`) LIKE LOWER(?)';
+		}
+
+		$stmt = OC_DB::prepare('SELECT `gid` FROM `*PREFIX*groups`' . $searchLike . ' ORDER BY `gid` ASC', $limit, $offset);
+		$result = $stmt->execute($parameters);
 		$groups = array();
 		while ($row = $result->fetchRow()) {
 			$groups[] = $row['gid'];
@@ -212,10 +219,17 @@ class OC_Group_Database extends OC_Group_Backend {
 	 * @return array an array of user ids
 	 */
 	public function usersInGroup($gid, $search = '', $limit = null, $offset = null) {
-		$stmt = OC_DB::prepare('SELECT `uid` FROM `*PREFIX*group_user` WHERE `gid` = ? AND `uid` LIKE ? ORDER BY `uid` ASC',
+		$parameters = [$gid];
+		$searchLike = '';
+		if ($search !== '') {
+			$parameters[] = '%' . $search . '%';
+			$searchLike = ' AND `uid` LIKE ?';
+		}
+
+		$stmt = OC_DB::prepare('SELECT `uid` FROM `*PREFIX*group_user` WHERE `gid` = ?' . $searchLike . ' ORDER BY `uid` ASC',
 			$limit,
 			$offset);
-		$result = $stmt->execute(array($gid, '%'.$search.'%'));
+		$result = $stmt->execute($parameters);
 		$users = array();
 		while ($row = $result->fetchRow()) {
 			$users[] = $row['uid'];
@@ -231,8 +245,15 @@ class OC_Group_Database extends OC_Group_Backend {
 	 * @throws \OC\DatabaseException
 	 */
 	public function countUsersInGroup($gid, $search = '') {
-		$stmt = OC_DB::prepare('SELECT COUNT(`uid`) AS `count` FROM `*PREFIX*group_user` WHERE `gid` = ? AND `uid` LIKE ?');
-		$result = $stmt->execute(array($gid, '%' . $search . '%'));
+		$parameters = [$gid];
+		$searchLike = '';
+		if ($search !== '') {
+			$parameters[] = '%' . $search . '%';
+			$searchLike = ' AND `uid` LIKE ?';
+		}
+
+		$stmt = OC_DB::prepare('SELECT COUNT(`uid`) AS `count` FROM `*PREFIX*group_user` WHERE `gid` = ?' . $searchLike);
+		$result = $stmt->execute($parameters);
 		$count = $result->fetchOne();
 		if($count !== false) {
 			$count = intval($count);
diff --git a/lib/private/user/database.php b/lib/private/user/database.php
index f2fa0cc39ce..d080bff04b5 100644
--- a/lib/private/user/database.php
+++ b/lib/private/user/database.php
@@ -148,11 +148,19 @@ class OC_User_Database extends OC_User_Backend implements \OCP\IUserBackend {
 	 * Get a list of all display names and user ids.
 	 */
 	public function getDisplayNames($search = '', $limit = null, $offset = null) {
+		$parameters = [];
+		$searchLike = '';
+		if ($search !== '') {
+			$parameters[] = '%' . $search . '%';
+			$parameters[] = '%' . $search . '%';
+			$searchLike = ' WHERE LOWER(`displayname`) LIKE LOWER(?) OR '
+				. 'LOWER(`uid`) LIKE LOWER(?)';
+		}
+
 		$displayNames = array();
 		$query = OC_DB::prepare('SELECT `uid`, `displayname` FROM `*PREFIX*users`'
-			. ' WHERE LOWER(`displayname`) LIKE LOWER(?) OR '
-			. 'LOWER(`uid`) LIKE LOWER(?) ORDER BY `uid` ASC', $limit, $offset);
-		$result = $query->execute(array('%' . $search . '%', '%' . $search . '%'));
+			. $searchLike .' ORDER BY `uid` ASC', $limit, $offset);
+		$result = $query->execute($parameters);
 		while ($row = $result->fetchRow()) {
 			$displayNames[$row['uid']] = $row['displayname'];
 		}
@@ -220,8 +228,15 @@ class OC_User_Database extends OC_User_Backend implements \OCP\IUserBackend {
 	 * Get a list of all users.
 	 */
 	public function getUsers($search = '', $limit = null, $offset = null) {
-		$query = OC_DB::prepare('SELECT `uid` FROM `*PREFIX*users` WHERE LOWER(`uid`) LIKE LOWER(?) ORDER BY `uid` ASC', $limit, $offset);
-		$result = $query->execute(array('%' . $search . '%'));
+		$parameters = [];
+		$searchLike = '';
+		if ($search !== '') {
+			$parameters[] = '%' . $search . '%';
+			$searchLike = ' WHERE LOWER(`uid`) LIKE LOWER(?)';
+		}
+
+		$query = OC_DB::prepare('SELECT `uid` FROM `*PREFIX*users`' . $searchLike . ' ORDER BY `uid` ASC', $limit, $offset);
+		$result = $query->execute($parameters);
 		$users = array();
 		while ($row = $result->fetchRow()) {
 			$users[] = $row['uid'];