Long, dark night of “the loop”…

  • I’ve gone from 0 to just over 2000 posts on my site in just under two months. My initial render time was about 0.10 seconds and it’s now over 2.0 seconds. This is worrying and I need to understand more about why performance is degrading so quickly.

    The way I understand “the loop”, WP is pulling the 10 most recent posts, then stopping and displaying a page with a navigation menu. (I’m using Scriptygoddess’ WP Paginate plugin for the nav menu. It displays the total number of pages, which I assumes it calculates from a table’s row count.)

    If that’s how the loop works, then why would it make any difference to render time how many records there are?

    I know there are caching plugins, etc., that can help here. At the moment, though, I just want to understand what the problem is. I’m hoping to build this database at a rate of 15,000 entries a year, but I’m not a programmer and don’t want to have to hack my way to acceptable performance. Should I be looking at a different platform (e.g., Drupal or some other “enterprise” CMS)? Can WP manage a database of that size efficiently?

    I’d appreciate any thoughts or advice.

Viewing 10 replies - 46 through 55 (of 55 total)

  • hooopla:

    Upon closer inspection, it looks like queries [2]-[6] are being added by the plugin ([7] onwards seems to match more closely with [2]+ in the non-plugin version). Either the plugin is executing these queries directly or else it is forcing WordPress to do another pass through “the loop”. The latter is where I would start.

    The second loop ([7]+) appears more normal and uses your setting of 10 posts per page.

    My guess is that the plugin hijacks the first pass through “the loop” to count the number of posts available. Then it starts a second pass with the settings back to normal for output to the user. If that’s all wp-paginate needs, a better approach would be to do a single query to count the posts rather than forcing the loop to do it.

    Karel,
    You need to add the SAVEQUERIES line to the *real* index.php, not the one in your theme. Look in your site’s root directory (normally where you installed WordPress).

    <code>wp_footer(); does the same thing as <code>do_action('wp_footer'); – I’m just used to the old style so that’s my error. I’ve updated the instructions.

    Denis-de-Bernardy:
    I lack words to tell you how much I hate MySQL, its pathetic features, and its pathetic performance.
    Maybe it does lack some necessary features but there can be no doubt about MySQL’s performance, second only to Oracle(yes, both are better than SQL Server which is quite slow as I’ve seen it & no, I’m not a Microsoft hater, I usually use & love Microsoft products a lot). Here’s a Benchmark done in 2002 & that’s talking about MySQL 4.0.1 & mind you, a lot of webhosts still have MySQL 4.0.x on their servers, not everyone’s got v4.1.x yet!! And in that benchmark, Oracle9i came out as best but it was just slightly better than MySQL which came in second!! So you possibly can’t say that MySQL has poor performance, as I know it, MySQL powers a lot of high-traffic websites which get a lot of traffic, much more than hooopla’s website I think!!

    If your script doesn’t work good, then you are to try to improve it & then also tweak the way you use the RDBMS for maximum performance!! ??

    Though its a point that MySQL does lack a lot of necessary features like Triggers, Stored Procedures, etc. that are a must in a good RDBMS but they are in development in v5 of MySQL. Besides we can hope for good products from MySQL despite it being a free database as it does has a commercial side as well, so the devs there are not volunteers but paid employees!!

    But the main thing that I noticed is that in LightPress, the developer has optimised the SQL a lot, so maybe WordPress can take a leaf out of its book and instead of putting in new features in next few releases, why not focus more on optimising the software so that it runs better than ever? I certainly would like to see WordPress run better & faster than now, in next releases rather than see any new feature(s)!!

    Thanks, darkcanuck! Works wonderfully now. My hosting provider seems to have some trouble, but I really wanted to exclude problems on my side.

    BTW, I hacked the code a bit, so there is always the < !– x queries/y seconds –> output.

    Create an index on the date posted field ??

    https://dev.mysql.com/doc/mysql/en/create-index.html

    I actually have some custom code I use on my site, that caches queries for common data. I’ll have to think if there’s any way to make it into a plugin (right now it’s custom function calls, as they need to intercept the database query before handing to $wpdb…).

    -d

    Thread Starter hooopla

    (@hooopla)

    Create an index on the date posted field ??

    I’m probably going to do that. Thanks for the link.

    I think you shouldn’t index on the date. Rather order it by the id. id’s are automatcially indexed and it will be really fast.

    angsuman,
    you can have multiple indices into a table and they can be either single or multi-column. In general, a query will be faster if all of the fields used by WHERE clauses are indexed (separately or, even better, all together).

    So if the most significant WHERE clause uses the date, the query will execute much faster if the date is indexed.

    This is a bit of a simplification of course, query optimization is a fairly complex topic.

    @darkcanuck
    I am aware darkcanuck, have long rdbms experience ??

    I was just suggesting him a simple route. Indexes cost space too and many web hosting providers have limited space which includes database too.

    Personally I prefer not to use date based ordering as ID (auto generated) based ordering works just as well and technically more accurate. Primary keys are anyway indexed.

Viewing 10 replies - 46 through 55 (of 55 total)
  • The topic ‘Long, dark night of “the loop”…’ is closed to new replies.