wordpress mysql优化_wordpress数据库优化

如果你需要转换成innodb，请执行：ALTER TABLE wp_downloads ENGINE=InnoDB;

如果出现：#1067 - Invalid default value...错误

修改下数据库配置文件，在[mysqld]下面添加：sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

mysql8.0添加上面的就行了，如果是5.7添加：sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

这个1067错误表示，之前的版本不兼容InnoDB，因为InnoDB不支持时间为0的，所以改下参数。

好了，现在开始优化Wordpress数据库，记得根据自己的数据库前缀改。

同时数据库默认类型改成：(也是修改数据库配置文件)default_storage_engine = InnoDB

1：删除已被删掉的评论DELETE FROM wp_comments WHERE comment_approved = 'trash';

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

2：删除不用的wp_postmeta元信息表DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';

DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';

3：删除文章修订DELETE FROM wp_postmeta WHERE post_id IN (SELECT id FROM wp_posts WHERE post_type = 'revision');

DELETE FROM wp_term_relationships WHERE object_id IN (SELECT id FROM wp_posts WHERE post_type='revision');

DELETE FROM wp_posts WHERE post_type='revision';

//或者全部

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'

4：删除之前的pingback记录和引用(如果网站一开始就关闭了，就用不到)DELETE FROM wp_comments WHERE comment_type = 'pingback';

DELETE FROM wp_comments WHERE comment_type = 'trackback';

5：删除与文章无关的标签DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );

DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);

DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

优化全部多余记录：OPTIMIZE TABLE wp_commentmeta;

OPTIMIZE TABLE wp_comments;

OPTIMIZE TABLE wp_links;

OPTIMIZE TABLE wp_options;

OPTIMIZE TABLE wp_postmeta;

OPTIMIZE TABLE wp_posts;

OPTIMIZE TABLE wp_terms;

OPTIMIZE TABLE wp_term_relationships;

OPTIMIZE TABLE wp_term_taxonomy;

OPTIMIZE TABLE wp_usermeta;

OPTIMIZE TABLE wp_users;

当然也可以使用数据库优化插件WP Optimize，以上代码部分参考：