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