wp_admin edit.php slow with lots of queries

I have about 4,000 posts. Currently when I click on All Posts (edit.php) it’s taking about 10 seconds to load and I’m showing approximately 1000 queries!

Most of them look like…

Read More
SELECT t.*, tt.* FROM wp_terms AS t 
INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id 
WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (37336) 
ORDER BY t.name ASC

If I deactivate WordPress SEO, I can cut the number of queries in half but the page still takes approximately 7 seconds to load.

Just for fun, I deactivated all my plugins to no significant performance improvement. I’ve tweaked MySQL and PHP in about every way I can think of. The issue is the exorbitant amount of queries.

Any way to tweak WordPress here to use less? I have tried deleting revisions, etc. Nada. Really could use some insight here.

Thanks!

Related posts

Leave a Reply

2 comments

  1. One way to do this (and yes, it is ugly and hackish) is to create a custom page having a custom page template that draws out all of your posts and creates the proper admin url links for each one.

    For example:

    <?php
    /*
    Template Name: View Posts Quickly
    */
    if ( !is_user_logged_in() ) {
       # Redirect the user or use wp_die('Permission denied') or throw a 404
    } else if ( !current_user_can('activate_plugins') ) {
       # Permission denied
    } else {
       $query_args = array(
          'post_type' => 'post',
          'posts_per_page' => '-1',
          'post_status' => array( 'any' ),
          'orderby' => 'title',
          'order' => 'asc'
       );
       $post_list = new WP_Query( $query_args );
       if ( ! $post_list->have_posts() ) {
          # Tell user no posts found
       } else {
          echo '<h2>The Post List:</h2><ul>';
          while ( $post_list->have_posts() ) {
             $current = $post_list->next_post();
    
             # Modify the below to include any extra data wanted for each item
             echo get_post_edit_link( $current->post_title, '<li>', '</li>', $current->ID);
          }
          echo '</ul>';
       }
    }
    ?>
    

    Obviously the above could be turned into an actual admin page if wanted. I do it on my own sites when I want to have a very different view of things on the back end.

    Using the above (and, of course, creating a private page and setting the template to use the above template) will get one a cleaner, faster list.

    But the normal display of wordpress posts displays a lot of extra data using a lot of extra queries due to the database design. The db design gives good flexibility, but one trade off is the ability to draw lots of related information in just one or two queries.

    A better way might be to kill those extra queries by stripping out the term columns displayed in the post list (but you would lose the various sorting, limiting, etc. tools that the wordpress list gives, though you could build your own filters, sorting, etc. in as wanted) using the manage_*_posts_columns filter.

  2. Another, better, way to do this would be to do a couple of different things:

    1. Locate the filters that are running those queries in wordpress SEO and remove them
    2. Look at the data they are gathering to display in the list of posts and use the posts_join and posts_fields filters to add the data they are gathering via their individual queries into the query being ran to generate the post list.

    For example, the query you showed:

    SELECT t.*, tt.* FROM wp_terms AS t 
    INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id 
    INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id 
    WHERE tt.taxonomy IN ('post_tag') AND tr.object_id IN (37336) 
    ORDER BY t.name ASC
    

    This query is gathering term_taxonomy and term_relationships entry for some purpose (most likely to show which posts have had seo type things done to them.

    If you figure out specifically what is being looked for, you could then come up with appropriate query segments to alter to pull out just what is needed for each post.

    If you can do this to simply add a couple more fields for each post to the results list without making the post list return multiple records for each post, then you could remove their display filter as well and have it simply pull the fields from the current post.

    This would basically remove all of the extra queries inserted by WP SEO and let you get the data that they display for almost no extra work (just selecting two additional fields from the database).

    If they are using a serialized array for each post, you will probably need to do something like:

    posts_join

    append something like LEFT JOIN (post_meta) ON (post_meta.post_id = post.ID)

    posts_fields

    append something like (post_meta.meta_value) AS seo_meta

    Then remove their filter on the columns and add your own that simply unserializes the seo_meta value and draws the appropriate data out.

    If they aren’t using a serialized array, then you would just make a few extra left joins (which will set seo_meta to null if it does not exist) and a few extra field names.

    I’m actually doing this in a few of my plugins and tracked down all of the details that I needed to do it via the following (along with digging through some of the source):

    Notes:

    • If doing the above, make sure to use is_admin() and appropriate other checks to modify the query only in the admin side when grabbing lists of items for the appropriate screens.