WordPress Rewrite rules – ballooning wp-options table

When xNepali blog grew in size, the database load on the server was huge. We had to move to the Virtual Private Servers to deal with. But, to our surprise, we had to constantly play around with the memory to get the optimum result. There were a lot of times when database ‘max_allowed_packet’ bytes error were observed in error logs.

To solve the database connection we tried adding another private server for database. But, it didn’t solve anything.

In the blog there were about 400 pages. Somebody suggested that a lot of pages were not good for the server. We started migrating the pages to posts as post have better flexibility and more keyword options (like tags) than pages. Now, the number of pages are only about 200. But, the problem is still the same.

I checked the rewrite-rules column in the wp-options table. It was almost 1mb in size.  There are
if( is_array( $attachment_uris ) ) {
foreach ($attachment_uris as $uri => $pagename) {
$this->add_rewrite_tag('%pagename%', "($uri)", 'attachment=');
$rewrite_rules = array_merge($rewrite_rules, $this->
generate_rewrite_rules($page_structure, EP_PAGES, true, false));
}
}
In addition, I added the following code in the function.php in the theme:


function filter_rewrite_attachment($content) {
if (!is_array($content))
return $content;
foreach ($content as $key => $val) {
if (strpos($val, 'attachment') !== false)
unset($content[$key]);
}
return $content;
}
add_filter('page_rewrite_rules', 'filter_rewrite_attachment');
add_filter('post_rewrite_rules', 'filter_rewrite_attachment');


The first code didn’t have much effect in the database size. The second code by Johan Eenfeldt from wp-hackers group did help reducing the table size. I need to wait for at least a day to see what performance enhancement or problem it will cause.

Incoming search terms:

• wordpress rewrite rules images
• wordpress rewrite_rules slow
• wp options size wordpress
• wp rewrite reset
• wp-option rewrite rules

WordPress – database

This post is for myself. I usually like to change database and forget the commands. So this is a reference sheet for me while dealing with my WordPress database.

There usually are 11 default tables – with "wp_" prefix.

wp_options is the one I usually deal with while changing the directory, website address and so on.

Renaming tables

RENAME TABLE old_posts TO wp_posts;

Merging two tables (when I manually merge posts from two separate blogs. )

CREATE TABLE wp_post_merged SELECT DISTINCT * FROM (SELECT * FROM wp_post_1 UNION ALL SELECT * FROM wp_post_2) sq;

….

Incoming search terms:

• technology wordpress database