3

Hi
i know this question been discussed so many times.
But i have a specific question with large table in WordPress blog.
I have around 50K post with more than 100K post tag.
When doing search it takes a lot of memory and cpu resource.
Currently i'm using MyISAM for both wp_posts and wp_posts_meta.
So i would like to know your opinion, should i change it to InnoDB?

The WordPress blog host on my vps with 2GB RAM and 4 core CPU using CentOS 5. And i have tried to optimized the MySQL configuration plus using database cache plugin.

So will InnoDB solve my problem? And please forgive me if this been asked before. I just want to know your expert opinion.

Thanks

Ivan

Improve this question

3 Answers 3

Reset to default
3

Clearing post and page revisions will greatly reduce the size of a WP database - up to 90% in some cases - with a huge return in speed.

Run as SQL query in phpmyadmin to delete revisions; change table prefix as necessary:

DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'

Then optimize all tables. And then add

define ('WP_POST_REVISIONS', FALSE);

near the top of wp-config.php to disable future revisions.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Use EXPLAIN on your slow SELECT statements to see the queryplan. Now you can guess why things are slow and eat up all memory. Did you index the right columns? Is your SQL written correctly?

When most of the activity is read only, MyISAM should be fine. MyISAM also gives you full text search, what you might need in this case. innoDB doesn't have full text search.

Improve this answer

Comments

2

Switching table type won't speed up any searches. InnoDB will only help if you have locking issues. Since blog data doesn't change much, it's very unlikely that you are having locking issues. It sounds like the issue is with full text searching. I would look for a plugin that will add a full text index and use that index for searching.

Improve this answer

1 Comment

Yes, I'd go with that. There's a Sphinx plugin for WordPress specifically designed to help with this on large sites that need search, using the well-respected Sphinx engine. You'd need to install Sphinx, of course, but if it's your own VPS this shouldn't be a problem. There should be a package for CentOS.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.