Main $wp_query returning WAY too many posts.

  • Resolved Nathan Rice

    (@nathanrice)


    11 years, 10 months 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.

Viewing 15 replies - 1 through 15 (of 21 total)

  • 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.

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

    @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.

    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.

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

    Thread Starter Nathan Rice

    (@nathanrice)

    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.

    Thread Starter Nathan Rice

    (@nathanrice)

    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.

    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.

    Thread Starter Nathan Rice

    (@nathanrice)

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

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

    Thread Starter Nathan Rice

    (@nathanrice)

    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.

    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.

    Found one prob…

    https://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.

    Moderator Helen Hou-Sandi

    (@helen)

    Core Lead Developer and 4.0, 4.7, and 5.6 Release Lead

    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.

    Moderator Helen Hou-Sandi

    (@helen)

    Core Lead Developer and 4.0, 4.7, and 5.6 Release Lead

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

Viewing 15 replies - 1 through 15 (of 21 total)
  • The topic ‘Main $wp_query returning WAY too many posts.’ is closed to new replies.