WordPress fails with large databases

  • I am building a site from a large database that contains 620,000 posts. I am trying to convert this to wordpress. I am already with a wordpress installation up and running with 112,000 posts.

    The problem is that WP is working very bad with such large database. For example, I have the PHP blank page of death everytime I try to obtain a list of posts or a list of categories, or something involving a large number of posts. In other words: everytime WP has to fetch a large set of records from the database it fails.

    I have increased php memory to astronomical amounts to see if that was the problem, cached the entire site, optimized the database (that is just text), but WP simply was not designed with large databases in mind and cannot deal with more than perhaps 10 to 15 thousand posts.

    In the last 3 hours I am trying to obtain a list of all categories of my site, by alphabetical order, but all I have is the blank page of death.

    I would like to know if there’s someone out there that have arrived at the same sad conclusion as me… thanks.

Viewing 8 replies - 1 through 8 (of 8 total)

  • It isn’t the database, its how you are making the calls.

    Here’s an example: I have 600+ blogs in the blogroll, and the “blogroll” page was way too slow. I limited the number of blogs to 50, and the page now comes up very quickly.

    When you work with large amounts of data, the game changes. You have to be careful about how you access the data.

    Is this a dedicated server? Where is the SQL DB? How is the load on the DB’s box?

    ‘WP has to fetch a large set of records from the database it fails.’

    This sounds like a DB problem, not WP. “It” is the DB.

    Thread Starter HairyPotter

    (@hairypotter)

    SCREENER: I am not making the calls. WP is and version 2.3.3 is worst than 2.2. The old version can access the database faster.

    PINOY.CA: Yes it is a dedicated server and the MySQL server is on the same box.

    Run this query from PhpMyAdmin and if you get a blank page there, your DB didn’t have indices set up properly during the import — in which case I suggest you drop the WP tables, reinstall WP (which recreates the tables), then reload your data:

    SELECT cat_ID, cat_name, category_nicename, category_description, category_parent, category_count FROM wp_categories WHERE cat_ID > 0 ORDER BY cat_name asc

    This is a simple query. The “Recent Comments” query is more complex:

    SELECT comments.*, posts.* FROM wp_comments as comments INNER JOIN wp_posts as posts ON posts.ID = comments.comment_post_ID WHERE posts.post_date_gmt <= '2008-03-27 16:15:00' AND posts.post_password = '' AND comments.comment_approved = '1' AND ( posts.post_status = 'publish' OR posts.post_status = 'static' ) GROUP BY posts.ID ORDER BY comments.comment_date DESC LIMIT 5

    If PhpMyAdmin runs fast then I would say something else in your page layout is the culprit (or accessing a DB table that didn’t have indices set up properly). Check your MySQL log files: https://dev.mysql.com/doc/refman/4.1/en/log-files.html .

    Thread Starter HairyPotter

    (@hairypotter)

    These queries worked fast 0.0650 seconds. The problem is really with wordpress or more probably with php. Believe me. Imagine the time and memory that should be used by a query to list all 6,000 categories alphabetically. Php will never do that. It will timeout or crash before.

    > In the last 3 hours I am trying to obtain a list of all categories of my site, by alphabetical order, but all I have is the blank page of death.

    Output 6,000 records into only one webpage? That isn’t a blog page. No, PHP can’t do that. PhpMyAdmin will die, too. Yours isn’t a WP issue.

    Thread Starter HairyPotter

    (@hairypotter)

    so, you have a blog with 6,000 categories running on wordpress, what would you do to display the categories?

    If the categories aren’t hierarchical … I wouldn’t.

    I don’t think a website user will browse through four pages of T’s to find out you have ‘Thingamajigs’ listed. Imagine the frustration because she eventually find it under ‘Widgets’ instead?

    If I’m hard against the wall … I’d create custom page, navigable with an alphabar. The webpages would look like a page from a phone book. Maximum 300 categories per page. https://en.wikipedia.org/wiki/Special:AllPages/Wordpress is a possible design.

    However, if the categories are hierarchical … I would. I’d create something like https://www.thomasnet.com/browse .

    This is just my 2c.

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘WordPress fails with large databases’ is closed to new replies.