We have a user on our network that currently has a wordpress database that is quite large, here is a sample query from our slow query logs:
# Time: 050726 11:39:18
# User@Host: xxxxx[xxxxx] @ localhost []
# Query_time: 16 Lock_time: 0 Rows_sent: 10 Rows_examined: 207180
use xxxxx;
SELECT DISTINCT * FROM wp_posts WHERE 1=1 AND post_date_gmt <= '2005-07-26
18:39:59' AND (post_status = "publish") GROUP BY wp_posts.ID ORDER BY
post_date DESC LIMIT 0, 10;
There appears to be the appropriate indexing in wp_posts -- is there anyway we can assist this user in trimming down his 208,000 table rows?
OPTIMIZE TABLE, perhaps? How did he get 207,000 posts? Nevermind, I don't want to know.
Yeah I don't think the Optimize Table sequence is going to do much good here because we aren't talking about a bunch of left over Deletes, there are legitimately 207,000 rows here.
The client appears to be cross referencing and pulling feeds from other sources. I'm not very familiar with blogging/rss to understand what he's doing here, he did have a cron setup to run every minute, auto/post.php (Is this a script included with wordpress for getting feeds?)
Thanks for that link, I will recommend the client give it a go, we may end up implementing it for him just to get it done quicker.
Besides that, is there no realistic way to archive and then remove rows in wp_posts?
Client is also curious, we'd like to remove about 180,000 of the posts.
If for instance you wanted to remove posts that were made between two dates, that could be done with an sql query. If he wanted to more selective ... could be very tricky / long-winded.
If the client is also curious, it strikes me that maybe he should have looked a bit more at the effect of the code, and has he indicated that he will abandon what he is currently doing ?
and there's no reason a cron job has to run every minute! that could be turned down.
No, there is no 'reblogging' script or plugin provided with WP.
Yes, wp-cache is the best thing to throw in there. Note however that because it makes the pages basically static, some 'dynamic' portions of the page may have to be coded up differently as includes that conform to a format wp-cache understands.
-d
redux
