0

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.

enter image description here

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.