posts - wp_posts table extremely large

admin管理员组

文章数量:1122832

my posts table in my WordPress database is nearly 1GB in size despite only having 3,409 posts, which is vastly larger than other WordPress databases I have. None of the rows in the posts table seem to have any particularly long entries. I have checked the maximum length of every single column in the table as follows:

select max(char_length(`ID`)) from wpwn_posts;
select max(char_length(`post_author`)) from wpwn_posts;
select max(char_length(`post_date`)) from wpwn_posts;
select max(char_length(`post_date_gmt`)) from wpwn_posts;
select max(char_length(`post_content`)) from wpwn_posts;
select max(char_length(`post_title`)) from wpwn_posts;
select max(char_length(`post_excerpt`)) from wpwn_posts;
select max(char_length(`post_status`)) from wpwn_posts;
select max(char_length(`comment_status`)) from wpwn_posts;
select max(char_length(`ping_status`)) from wpwn_posts;
select max(char_length(`post_password`)) from wpwn_posts;
select max(char_length(`post_name`)) from wpwn_posts;
select max(char_length(`to_ping`)) from wpwn_posts;
select max(char_length(`pinged`)) from wpwn_posts;
select max(char_length(`post_modified`)) from wpwn_posts;
select max(char_length(`post_modified_gmt`)) from wpwn_posts;
select max(char_length(`post_content_filtered`)) from wpwn_posts;
select max(char_length(`post_parent`)) from wpwn_posts;
select max(char_length(`guid`)) from wpwn_posts;
select max(char_length(`menu_order`)) from wpwn_posts;
select max(char_length(`post_type`)) from wpwn_posts;
select max(char_length(`post_mime_type`)) from wpwn_posts;
select max(char_length(`comment_count`)) from wpwn_posts;

None of these show exceedingly long maximum lengths or anything out of the ordinary - the longest post is just over 10000 characters. I've included a screenshot from phpmyadmin. What might be causing this? I have had an issue with malware infecting my server so want to make sure there is nothing malicious.

my posts table in my WordPress database is nearly 1GB in size despite only having 3,409 posts, which is vastly larger than other WordPress databases I have. None of the rows in the posts table seem to have any particularly long entries. I have checked the maximum length of every single column in the table as follows:

select max(char_length(`ID`)) from wpwn_posts;
select max(char_length(`post_author`)) from wpwn_posts;
select max(char_length(`post_date`)) from wpwn_posts;
select max(char_length(`post_date_gmt`)) from wpwn_posts;
select max(char_length(`post_content`)) from wpwn_posts;
select max(char_length(`post_title`)) from wpwn_posts;
select max(char_length(`post_excerpt`)) from wpwn_posts;
select max(char_length(`post_status`)) from wpwn_posts;
select max(char_length(`comment_status`)) from wpwn_posts;
select max(char_length(`ping_status`)) from wpwn_posts;
select max(char_length(`post_password`)) from wpwn_posts;
select max(char_length(`post_name`)) from wpwn_posts;
select max(char_length(`to_ping`)) from wpwn_posts;
select max(char_length(`pinged`)) from wpwn_posts;
select max(char_length(`post_modified`)) from wpwn_posts;
select max(char_length(`post_modified_gmt`)) from wpwn_posts;
select max(char_length(`post_content_filtered`)) from wpwn_posts;
select max(char_length(`post_parent`)) from wpwn_posts;
select max(char_length(`guid`)) from wpwn_posts;
select max(char_length(`menu_order`)) from wpwn_posts;
select max(char_length(`post_type`)) from wpwn_posts;
select max(char_length(`post_mime_type`)) from wpwn_posts;
select max(char_length(`comment_count`)) from wpwn_posts;

None of these show exceedingly long maximum lengths or anything out of the ordinary - the longest post is just over 10000 characters. I've included a screenshot from phpmyadmin. What might be causing this? I have had an issue with malware infecting my server so want to make sure there is nothing malicious.

• posts
• database

Share Improve this question asked Oct 9, 2019 at 4:47 user176444user176444 311 silver badge22 bronze badges 1

• Do you have any revisions stored in that DB? Could you show the counts of posts grouped by post_type? – Krzysiek Dróżdż Commented Oct 9, 2019 at 5:58

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Most of the space in that table in your case is taken by the overhead. 919MB is full size, with 885MB of overhead. Overhead is caused by the storage and index write operations in the MyISAM database engine storage. You can run this query to clear overhead:

OPTIMIZE TABLE `wpwn_posts`

Or, you can convert all tables to InnoDB engine, it is the default MySQL engine now, and it is better in dealing with the overhead and just better than MyISAM engine your table is using now.

本文标签： postswpposts table extremely large