WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Main $wp_query returning WAY too many posts. (22 posts)

  1. nathanrice
    Member
    Posted 1 year ago #

    I can usually troubleshoot issues pretty easily, but this one has me stumped.

    I'm working on an old site, 5 years of content, over 4000 posts, loads of comments and images, etc. It's completely up to date, no strange plugins active, and just to make sure it wasn't a theme issue, I did my testing in TwentyTwelve.

    The homepage has been running quite slow lately, so I did some debugging.

    First, the relevant debug bar query results:

    SELECT SQL_CALC_FOUND_ROWS w1L7f03Z_posts.ID FROM w1L7f03Z_posts WHERE 1=1 AND w1L7f03Z_posts.post_type = 'post' AND (w1L7f03Z_posts.post_status = 'publish' OR w1L7f03Z_posts.post_status = 'private') ORDER BY w1L7f03Z_posts.post_date DESC LIMIT 0, 12
    
    SELECT FOUND_ROWS()

    Nothing too strange here. I have 12 selected as my posts per page in Reading settings.

    But, when I go to load the page, it takes FOREVER ... like it's literally trying to loop through and display every post in the database. Honestly, I never let it finish, but I suspect this is exactly what's happening ... it is trying to loop through all 4000+ posts.

    So, before the loop in index.php (twentytwelve), I pasted this in:

    <?php
    global $wp_query;
    echo 'Debugging. Nothing to see here.<br /><br />';
    echo '$wp_query = ' . count( $wp_query->posts );
    echo '</div></div>';
    wp_footer();
    exit;
    ?>

    This is how I got my debug bar results.

    Sure enough, the count() on the $wp_query->posts result is 4337.

    Does anyone have any idea what could be causing this? I can provide other info, if it will help.

    FWIW, I disabled all plugins and it's still happening, so I don't think I can blame a theme or plugin.

  2. Chris Wallace
    Member
    Posted 1 year ago #

    If you can do this, create a second WordPress site (fresh install, do not copy anything from other site) as a virtualhost then run an export on the original site using the built-in WordPress. Then run the importer on your brand new install and see if you still have the same issues.

    My only guess is you have some cray cray code somewhere getting cray crazy.

  3. Jared Atchison
    Member
    Posted 1 year ago #

    If he is running 2012 and has all the plugins deactivated then I'm not sure where the crazy code would be hiding though.

  4. Chris Wallace
    Member
    Posted 1 year ago #

    @Jared Agreed, but it sounds like he's done all the proper debugging to figure out what the issue is. The few remaining variables are installation-specific files and database values. If you start with a fresh set of files and run the export/import functions, that should sanitize your data and at least get you closer to a fix.

    Not sure what else I could even think to recommend besides updating all the files in the install and running the post export.

    If, after all that, you determine that it's still running strange queries, I'd start looking at mysql and PHP versions and config files.

  5. jtallant
    Member
    Posted 1 year ago #

    It's possible the problem lies in the SQL query itself.
    SQL_CALC_FOUND_ROWS could be causing it to hang.

    Have you tried removing the loop and running your own query?

    You could write raw SQL in there. If your query runs faster, then you know the SQL generated by WP needs better optimization.

  6. Chris Wallace
    Member
    Posted 1 year ago #

    @jtallant But why would this be occurring only on this particular WordPress installation? MySQL configuration problem perhaps?

  7. nathanrice
    Member
    Posted 1 year ago #

    Migrating via the WP export/import features is absolutely impossible. Too many posts, too many comments, and WAY to many image attachments to practically move over. When changing servers, we almost always have to move the files manually, and do a SQL dump to get it to the new server.

  8. nathanrice
    Member
    Posted 1 year ago #

    BTW, if I try to do a new query ...

    $q = new WP_Query( array( 'posts_per_page' => 10 ) );

    ... and count() those results, same thing happens. But as soon as I add a category or author parameter, it starts honoring the posts_per_page parameter.

    Possibly unhelpful or unrelated, but strange nonetheless.

  9. Steve Blackwood
    Member
    Posted 1 year ago #

    Do you have access to PHPMyAdmin? You need to debug MySQL statements on the db itself if you can, and take WP out of the equation. Then you can actually let the query finish, since the returned rows might give a clue to the problem. For instance, I once had a bug in my where clause that caused the same 20 rows to be returned 100 times. Just seeing that told me what the problem was.

    I would also recommend culling parts of the query to pinpoint the troublesome part. Take the whole parenthesis out, for example, and whittle down 'til it works as expected.

  10. nathanrice
    Member
    Posted 1 year ago #

    Steve,
    Unfortunately that's over my head. I know very little about raw SQL interaction.

  11. Steve Blackwood
    Member
    Posted 1 year ago #

    Maybe give a trusted compatriot access... Hard to debug MySQL without working with the db itself. <shrug>

  12. nathanrice
    Member
    Posted 1 year ago #

    One thing I have done to circumvent this issue is to simply set a static homepage. If I'm gonna waste a query, at least this is a simple one to waste. (the homepage content is going to be custom queries anyway, so I really should have done this ages ago).

    The bug still irritates me, but at least I'm getting decent performance now.

  13. Steve Blackwood
    Member
    Posted 1 year ago #

    I'll try to duplicate the query (and bug) later tonight on an old WP db I have access to... but might not work on different data.

  14. Steve Blackwood
    Member
    Posted 1 year ago #

    Found one prob...

    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

    The sql_calc_found_rows returns a count of all rows found, as if there were no limit clause. So, it looks thru all rows, regardless of any limit. Remove that and see what happens.

  15. Helen Hou-Sandi
    WordPress Dev
    Posted 1 year ago #

    Pagination means it has to do total found rows to calculate. If you don't need pagination, you can set no_found_rows to true and it won't do that anymore. Whenever I do a one-off WP_Query instance that doesn't need pagination I set that arg for better performance.

  16. Helen Hou-Sandi
    WordPress Dev
    Posted 1 year ago #

    Meant to add that I could be wrong that that would help, but an overall thought :)

  17. Steve Blackwood
    Member
    Posted 1 year ago #

    I checked your query against a WP install with 2500+ posts, and got this:

    Showing rows 0 - 11 (12 total, Query took 0.0002 sec)

    So, the problem isn't in the query syntax. Works as expected. I think you need to let WP complete the run (you said you stopped it prematurely above), and see what the actual output is. MySQL doesn't appear to be the bottleneck, unless you have a problem in your server's install. The only way to know for sure would be to run it on your own server, against the actual database... if you don't have that skillset, ask a trusted colleague. Gotta be something in the PHP side of things.

    Sorry I couldn't be more help. :(

    That was WITH the SQL_CALC_FOUND_ROWS command, btw, so that wasn't slowing things down noticeably.

  18. Mark Jaquith
    WordPress Lead Dev
    Posted 1 year ago #

    Posted this on Twitter, but for posterity:

    I would guess mu-plugins and a pre_get_posts filter that alters posts_per_page

    And to elaborate: the fact that it works when you pass in author/category probably means that something is making posts_per_page unlimited (or very high) for the home page specifically.

  19. Mark Jaquith
    WordPress Lead Dev
    Posted 1 year ago #

    The query looks fine — are you sure that's the right query you're looking at?

  20. Hit the permalinks page in: Dashboard > Settings > Permalinks and give them a save. Maybe some old rewrite rules in there are causing havoc from before the fancy new logic was put in.

  21. nathanrice
    Member
    Posted 1 year ago #

    Thanks to Mark Jaquith, this issue is resolved.

    Turns out, because we use sticky posts to populate a slider, and I never thought to clear out posts from being sticky after they no longer need to be featured, we had 4000+ sticky posts. The main query was pulling all these guys in, causing the massive query mentioned above.

    Cleared out the stickies (and wrote up a limiter so this doesn't happen again) and we're back to smooth queries.

    Thanks so much to Mark for hunting this down!

  22. Daan Kortenbach
    Member
    Posted 1 year ago #

    I've just ran into the same thing. Raw query resulted in two posts, actual WP_Query results 10 posts. It was those sticky posts.
    Thanks for figuring this out guys, saved me a couple more hours :)

Topic Closed

This topic has been closed to new replies.

About this Topic