Repository: wp-graphql/wp-graphql (language: php)

Issue:
Slow query in User Loader when querying for the public users
### Description

We have encountered a customer issue where the GraphQL endpoints were returning slow responses. When debugging the problem, we found the User Loader generates a slow query to determine if the user is public.

The slow query is generated in the line [/src/Data/Loader/UserLoader.php#L82](https://github.com/wp-graphql/wp-graphql/blob/4fb1694552dda340724ca5ccf5d823256d080baf/src/Data/Loader/UserLoader.php#L82).

```
SELECT DISTINCT
    `post_author`
FROM
    wp_2_posts
WHERE
    (
        (
            post_type = 'post' AND(post_status = 'publish')
        ) OR(
            post_type = 'page' AND(post_status = 'publish')
        ) OR(
            post_type = 'l-page' AND(post_status = 'publish')
        ) OR(
            post_type = 'place' AND(post_status = 'publish')
        )
    ) AND `post_author` IN(1, 2, 3, 4, 5)
LIMIT 5;
```

```
EXPLAIN SELECT DISTINCT post_author FROM wp_2_posts WHERE ( ( post_type = 'post' AND(post_status = 'publish') ) OR( post_type = 'page' AND(post_status = 'publish') ) OR( post_type = 'l-page' AND(post_status = 'publish') ) OR( post_type = 'place' AND(post_status = 'publish') ) ) AND post_author IN(1, 2, 3, 4, 5) LIMIT 5;

+----+-------------+------------+------------+-------+----------------------------------------------------------------------------------------------+-------------+---------+------+--------+----------+-----------------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                                | key         | key_len | ref  | rows   | filtered | Extra                                               |
+----+-------------+------------+------------+-------+----------------------------------------------------------------------------------------------+-------------+---------+------+--------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | wp_2_posts | NULL       | range | post_author,yoast2_index,posts_mime_type_status_index,status_type_sort_date,type_status_date | post_author | 8       | NULL | 258277 |     8.17 | Using index condition; Using where; Using temporary |
+----+-------------+------------+------------+-------+----------------------------------------------------------------------------------------------+-------------+---------+------+--------+----------+-----------------------------------------------------+
```

In this case, the query scans 258K rows and takes 8-10 seconds to execute.

The slow query could be optimised with the query below, which returns results in 2 milliseconds.

```
SELECT DISTINCT
    `wp_users`.ID
FROM
    wp_2_posts
INNER JOIN wp_users ON wp_2_posts.post_author = wp_users.ID
WHERE
    (
        (
            post_type = 'post' AND(post_status = 'publish')
        ) OR(
            post_type = 'page' AND(post_status = 'publish')
        ) OR(
            post_type = 'l-page' AND(post_status = 'publish')
        ) OR(
            post_type = 'place' AND(post_status = 'publish')
        )
    ) AND `post_author` IN(1, 2, 3, 4, 5);
```

```
EXPLAIN SELECT DISTINCT wp_users.ID FROM wp_2_posts INNER JOIN wp_users ON wp_2_posts.post_author = wp_users.ID WHERE ( ( post_type = 'post' AND(post_status = 'publish') ) OR( post_type = 'page' AND(post_status = 'publish') ) OR( post_type = 'l-page' AND(post_status = 'publish') ) OR( post_type = 'place' AND(post_status = 'publish') ) ) AND post_author IN(1, 2, 3, 4, 5);
+----+-------------+------------+------------+-------+----------------------------------------------------------------------------------------------+-------------+---------+---------------------------------+-------+----------+-------------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                                                                                | key         | key_len | ref                             | rows  | filtered | Extra                                     |
+----+-------------+------------+------------+-------+----------------------------------------------------------------------------------------------+-------------+---------+---------------------------------+-------+----------+-------------------------------------------+
|  1 | SIMPLE      | wp_users   | NULL       | range | PRIMARY,user_login_key,user_nicename,user_email                                              | PRIMARY     | 8       | NULL                            |     5 |   100.00 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | wp_2_posts | NULL       | ref   | post_author,yoast2_index,posts_mime_type_status_index,status_type_sort_date,type_status_date | post_author | 8       | db190.wp_users.ID               | 14054 |     8.17 | Using where; Distinct                     |
+----+-------------+------------+------------+-------+----------------------------------------------------------------------------------------------+-------------+---------+---------------------------------+-------+----------+-------------------------------------------+
```

If you look at the `EXPLAIN`, it scans only 14K rows to fetch the results. 

The current query returns the user ID even when the user doesn't exist in the database. The new query returns the user ID only when the user exists in the database. I also removed the unnecessary `LIMIT` from the query.

Could you please patch the plugin with the optimised query? I'm happy to help with testing or answer any questions.

### Steps to reproduce

The issue can be reproduced from the GraphiQL IDE with the query below.

```
query PostCards {
  posts(first: 5) {
    nodes {
      title
    }
  }
}
```

### Additional context

_No response_

### WPGraphQL Version

1.22.1

### WordPress Version

6.4.5

### PHP Version

8.1

### Additional environment details

I tested this in the older WordPress and plugin versions, but I'm sure it can also be reproducible in the latest versions.

### Please confirm that you have searched existing issues in the repo.

- [X] Yes

### Please confirm that you have disabled ALL plugins except for WPGraphQL.

- [ ] Yes
- [ ] My issue is with compatibility with a specific WordPress plugin, and I have listed all my installed plugins (and version info) above.


Discussion / hints:
> The slow query is generated in the line /src/Data/Loader/UserLoader.php#L82.

Wondering if we even need to do a direct query here over something built-in (and possibly pre-cached), and if we do need our own why we're not caching it ourselves 🤔
@sathyapulse thanks for reporting! I know we had explored this issue in the past and had made _some_ progress, but perhaps not _enough_ progress. 

Going to link some things for reference: 

- https://github.com/wp-graphql/wp-graphql/issues/961
- https://github.com/wp-graphql/wp-graphql/pull/2031
- (maybe related): https://github.com/wp-graphql/wp-graphql/issues/1873

Fix patch:
diff --git a/src/Data/Loader/UserLoader.php b/src/Data/Loader/UserLoader.php
index 582371026..18ef28bfc 100644
--- a/src/Data/Loader/UserLoader.php
+++ b/src/Data/Loader/UserLoader.php
@@ -72,15 +72,13 @@ public function get_public_users( array $keys ) {
 		$public_only = true;
 
 		$where = get_posts_by_author_sql( $post_types, true, $author_id, $public_only );
-		$ids   = implode( ', ', array_fill( 0, count( $keys ), '%d' ) );
-		$count = count( $keys );
+		$ids   = implode( ', ', $keys );
 
 		global $wpdb;
 
-		$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.DirectQuery
+		$results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
 			$wpdb->prepare(
-				"SELECT DISTINCT `post_author` FROM $wpdb->posts $where AND `post_author` IN ( $ids ) LIMIT $count", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
-				$keys
+				"SELECT DISTINCT $wpdb->users.ID FROM $wpdb->posts INNER JOIN $wpdb->users ON post_author = $wpdb->users.ID $where AND post_author IN ( $ids ) ORDER BY FIELD( $wpdb->users.ID, $ids)" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPressVIPMinimum.Variables.RestrictedVariables.user_meta__wpdb__users
 			)
 		);
 
@@ -97,7 +95,7 @@ public function get_public_users( array $keys ) {
 		return array_reduce(
 			$results,
 			static function ( $carry, $result ) {
-				$carry[ (int) $result->post_author ] = true;
+				$carry[ (int) $result->ID ] = true;
 				return $carry;
 			},
 			[]
diff --git a/wp-graphql.php b/wp-graphql.php
index 3f2683fcc..a95b4c654 100644
--- a/wp-graphql.php
+++ b/wp-graphql.php
@@ -9,7 +9,7 @@
  * Version: 1.27.2
  * Text Domain: wp-graphql
  * Domain Path: /languages/
- * Requires at least: 5.0
+ * Requires at least: 5.9
  * Tested up to: 6.6
  * Requires PHP: 7.1
  * License: GPL-3


Test patch:
diff --git a/.github/workflows/testing-integration.yml b/.github/workflows/testing-integration.yml
index 43865571f..724cdecbd 100644
--- a/.github/workflows/testing-integration.yml
+++ b/.github/workflows/testing-integration.yml
@@ -58,11 +58,9 @@ jobs:
             php: '7.4'
           - wordpress: '5.9'
             php: '8.1'
+          # Minimum plugin requirement:
           - wordpress: '5.9'
             php: '7.3'
-          # Minimum plugin requirement:
-          - wordpress: '5.5' # Keeps compatibility with our test env.
-            php: '7.3' # Our build script requires PHP 7.3+.
 
     steps:
       - name: Checkout