Very inefficient SQL query

Resolved David Anderson / Team Updraft
(@davidanderson)

3 years, 3 months ago
On every page of the admin area, this stack of calls is made:
```
require_once('wp-admin/admin-header.php'), do_action('admin_enqueue_scripts'), WP_Hook->do_action, WP_Hook->apply_filters, Nelio_Content_Admin->register_assets, Nelio_Content_Admin->is_multi_author
```
That method makes a call to get_users() which generates an SQL query like this (the exact contents will depend on which roles on your site have the “edit posts” capability:
```
SELECT wp_users.*
FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )
WHERE 1=1 AND ( 
  ( 
    ( 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"edit\\_posts\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"administrator\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"author\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"contributor\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"editor\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"shop\\_manager\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"wpseo\\_manager\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"wpseo\\_editor\"%' )
    )
  )
)
ORDER BY user_login ASC
LIMIT 0, 2
```
This SQL query behaves very badly if you have significant numbers of users – because of the ORDER BY clause, it searches through *all* the meta values for all of those keys for all users in order to be able to apply the ordering; even though the caller doesn’t care at all about the order – it only wants to know “was there more than one result?”. Since meta_value in WordPress is not indexed, it results in a full row scan of all the results.

On the site I am examining this on, which has over 250,000 users, this query generally takes about 5 seconds.

i.e. Every page in the admin area suffers that delay because of that SQL query.

Unfortunately get_users() does not allow you to make a query without an ORDER BY clause. So you would need to use the users_pre_query WordPress filter to simply remove that clause from the query before it is executed.

i.e. Hook that filter in the method above, setting the query_orderby public property of the passed WP_User_Query object to the empty string. (Then unhook the filter after calling get_users() so that it doesn’t affect any other parts of WordPress). This will make the query execute very quickly, because it will then no longer need to row scan the meta value for every user on the site.

Viewing 6 replies - 1 through 6 (of 6 total)

Thread Starter David Anderson / Team Updraft
(@davidanderson)

3 years, 3 months ago
I have now tested my proposed solution, and confirm it works. In the method mentioned, replace:
```
$authors = get_users( $args );
```
with:
```
add_filter('users_pre_query', array($this, 'remove_get_users_sorting'), 10, 2);
$authors = get_users( $args );
remove_filter('users_pre_query', array($this, 'remove_get_users_sorting'), 10, 2);
```
And add the class method:
```
        public function remove_get_users_sorting($results, $wp_user_query) {
                $wp_user_query->query_orderby = '';
                return $results;
        }
```
The result of this for me was that a typical WP dashboard page was previously loading in 5.5 seconds, but afterwards in 0.4 seconds.
Thread Starter David Anderson / Team Updraft
(@davidanderson)

3 years, 3 months ago
Almost all that 0.4s is still the remaining query – now over 90% more efficient, but still has to do a lot of value-parsing. It’d be nice if the method is_multi_author() had an early filter in it so that I could just hard-wire the result “yes, my site has more than one user at this level” without having to run that SQL at all.

i.e.
```
$short_curcuit = apply_filters('nelio_is_multi_author', null);
if (null !== $short_circuit) return $short_curcuit);
```
Thread Starter David Anderson / Team Updraft
(@davidanderson)

3 years, 3 months ago

By the way, https://developer.wordpress.org/plugins/wordpress-org/detailed-plugin-guidelines/#4-code-must-be-mostly-human-readable requires that you ship human-readable code. Your JavaScript files are minified, without including the non-minified version as specified there. This prevents me analysing further how the “is multi author?” method actually gets used.

Plugin Author David Aguilera
(@davilera)

3 years, 3 months ago

Hi, David. Thanks for the analysis and your proposed solution. I’ll look into it tomorrow and implement it — the early filter to short-circuit the expensive SQL query makes total sense 🙂

Regarding the human readability of our code, we don’t ship it in the stable versions to make sure the plugin’s bundle size is as small as possible. However, if you download the trunk (i.e. Development Version), you’ll see the source code: https://wordpress.org/plugins/nelio-content/advanced/ I hope this helps.

Plugin Author David Aguilera
(@davilera)

3 years, 3 months ago

Morning, David. We just released a new version of our plugin with your proposed improvements. Thank you so much for reporting the issue and sharing several solutions with us.

PS. Regarding the filter to short-circuit, it’s finally named “nelio_content_is_multi_author”.

Thread Starter David Anderson / Team Updraft
(@davidanderson)

3 years, 3 months ago

Thank you!

David

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘Very inefficient SQL query’ is closed to new replies.