what php module creates this query & returns data for this source code?

class-wp-posts-list-table starts a process that issues a SQL query that returns all users to a dropdown box.

the SQL query is:
SELECT wp_users.ID,wp_users.user_login,wp_users.display_name FROM wp_users INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id) WHERE 1=1 AND ( (wp_usermeta.meta_key = 'wp_user_level' AND CAST(wp_usermeta.meta_value AS CHAR) != '0') ) ORDER BY display_name ASC /* From [vivinow.com/wp-admin/post.php?post=4192&action=edit] in [N/A] */;

Editing a post or page has the following all users dropdown source code:
<div id="authordiv" class="postbox hide-if-js" >
<div class="handlediv" title="Click to toggle">
</div><h3 class='hndle'><span>Author</span></h3>
<div class="inside">
<label class="screen-reader-text" for="post_author_override">Author</label>
<select name='post_author_override' id='post_author_override' class=''>
<option value='33' selected='selected'>Boyd Pearce</option>
<option value='40'>CarolJ</option>
<option value='45'>Donna Carter</option>
<option value='41'>JeremyL</option>
<option value='44'>KevinC</option>
<option value='42'>LarryP</option>
<option value='35'>MeganC</option>
<option value='1'>p2p</option>
<option value='49'>PatriciaC</option>
<option value='37'>PhilK</option>
<option value='36'>RichardL</option>
<option value='43'>RicMD</option>
<option value='34'>VinnieJ</option>
</select></div>
</div>
</div><div id="advanced-sortables" class="meta-box-sortables"></div></div>
</div>

With 130,000 members and growing,this cripples the site un-necessarily. Changing author can be done with a simple wpdb update if it is ever necessary.
At 150,000 where we were before deleting 30,000 members, page & post edits time out with a 504 error preventing any editing.

I have been through class-wp-posts-list-table, class-wp-users-list-table, list-table and many other modules without finding where this killer query is constructed.
Thanks,
Boyd

  • vivinow

    Hi Vinod,

    The slow query came from my hosting service(WPEngine) MySQL slow query log.

    I started looking for the problem when editing pages & posts (vivinow.com/wp-admin/Post.php) became very slow and ultimately starting having 504 time out errors.

    At least 2 important dashboard functions have the same problem all posts (wp-admin/edit.php) and all users (wp-admin/users.php).

    When I ask WPEngine for help, they pointed me to the slow query & I looked at the browser source code for the pages and found the same end result of the query which returned all users to the browser as a “hidden” dropdown:
    <label class="screen-reader-text" for="post_author_override">Author</label>
    <select name='post_author_override' id='post_author_override' class=''>
    <option value='33' selected='selected'>Boyd Pearce</option>
    <option value='40'>CarolJ</option>

    I rewrote the query to select only admin, editors and authors (<100 users) and began the hunt to find where to insert it.
    I quickly found out it came from inside core wordpress code and started with the problem: editing pages & posts and began the search through the source code directories and google searches.

    I found a way to turn off sending the query results to the browser for post.php and user.php (but not edit.php) by commenting out the code in edit-form-advanced.php below but haven’t found out where to fix the ‘all user’ query yet.

    To stop return to the browser of ‘all user’ result set, comment out
    Vivinow.com/wp-admin/edit-form-advanced.php
    /* (if ( post_type_supports($post_type, 'author') ) {
    if ( is_super_admin() || current_user_can( $post_type_object->cap->edit_others_posts ) )
    add_meta_box('authordiv', __('Author'), 'post_author_meta_box', null, 'normal', 'core'); }*/

    Post.php directory,
    Vivinow.com/wp-admin/post.php

    For Post.php, the next layer down is in wp-admin/includes/… directory
    Vivinow.com/wp-admin/includes/ list-table.php
    Defines function _get_list_table( $class, $args = array() ) which has a $coreclasses array with 'WP_Posts_List_Table' => 'posts' which sets up screen columns & args for class &
    loads require_once( ABSPATH . 'wp-admin/includes/class-wp-posts-list-table.php'
    Vivinow.com/wp-admin/includes/class-wp-posts-list-table.php
    List.table is sub-package of this class
    Vivinow.com/wp-admin/includes/class-wp-list-table.php
    List.table is sub-package of this class
    Vivinow.com/wp-admin/includes/ post.php (deals with an individual post)
    Vivinow.com/wp-admin/includes/ screen.php
    Vivinow.com/wp-admin/includes/ widgets.php
    Vivinow.com/wp-admin/includes/ meta-boxes.php

    I have found many calls to $wpdb but have found many SELECT COUNT but haven’t found the SELECT ‘all users problem’

    Help would be appreciated.

    Boyd

    For completeness,

    Edit.php and user.php directory,
    Vivinow.com/wp-admin/edit.php
    Vivinow.com/wp-admin/user.php

    For edit.php and user.php, the next layer down is also in wp-admin/includes/… directory
    Vivinow.com/wp-admin/includes/ class-wp-users-list-table.php
    Vivinow.com/wp-admin/includes/ user.php

  • vivinow

    I think a solution is to add the user IDs to the includes option of wp_dropdown_users function defined in wp-includes/user.php.

    Added my users and it solved the edit.php problem as well as post.php

    This is a well documented function with many options.

    I found only 1 google instance where someone had 100k members and broke WordPress with the same problem.

    Are there only small Wordpress sites in the world?

    Boyd

  • Vinod Dalvi

    Hi Boyd,

    Thank you for your reply.

    Before directly modifying WordPress core i recommend you to try other options like following.

    http://www.codeproject.com/Articles/729732/Optimizing-WordPress-Performance-with-Plugins
    http://codex.wordpress.org/WordPress_Optimization/WordPress_Performance
    https://wordpress.org/plugins/wp-super-cache/
    https://wordpress.org/plugins/w3-total-cache/

    Could you please try increasing memory allocated to PHP using the solutions posted on the following pages?

    http://docs.woothemes.com/document/increasing-the-wordpress-memory-limit/
    http://codex.wordpress.org/Editing_wp-config.php#Increasing_memory_allocated_to_PHP

    Are there only small Wordpress sites in the world?

    No it's not true.

    Find the more information on the following pages.

    http://www.wpbeginner.com/showcase/21-popular-brands-that-are-using-wordpress/
    http://vabulous.com/myths-debunked-on-why-large-companies-use-custom-designed-wordpress-sites/

    Regards,
    Vinod Dalvi

  • vivinow

    Vinod, thanks for the interesting articles and I have read them all. Unfortunately, none of them will solve this problem.

    WPEngine has cache, minify, optimized versions of MySQL and other solutions to speed up WordPress and good support as well. Thankfully, they identified the cause of my problem but had no idea on how to fix it other than removing all the users from the system.

    for the past two months, I have searched the internet, asked many people and dug through WordPress code for days and days. Many warned not to modify the core but nobody knew where the slow query originated except at the highest level, i. e., Post.php.

    Productivity cratered for all my authors, editors and myself. I removed 150,000 members (all but 100) from my development system and performance returned to great and 502's & 504s vanished.

    My background is database and there is no automated fix for poorly written queries or for shipping 10 megabytes per 100,000 members to every user reviewing posts or editing a post or page.

    Fortunately, the WordPress authors build an option in the function to include or exclude users that should be externalized as a config option.

    Including some users solves the problem. I have to shutoff updates so that this change will not be erased by WordPress updates but it works and performance has returned to acceptable.

    I would recommend you submit this as an enhancement request to have a config option.

    Thanks again,
    Boyd

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.