CODEWITHSUNDEEP
mysqlwordpresswoocommerceinnodb
Piotr Wiktor
Piotr Wiktor

Reputation: 23

Huge wp_post table

My client has a store on Woocommerce with 1.2 Gb database. I know that similar store (count by product ) should have approx 700Mb.

The biggest table is wp_posts (760Mb) alone ! Which, I think is strange. Usually biggest table is wp_postmete or wp_options.

I tried optimize this database by plugins: WP-Sweep and wp-optimize so there is no revisions and draft left.

I also tried SQL:

 OPTIMIZE TABLE

but it is innoDB so it do not support it. I get this message:

Table does not support optimize,doing recreate + analyze instead

So it is done? I mean:”recreate + analyze” or I should do it? And how?

I read that in innoDB i should dump table and restore but when I do this by DBeaver - i get same size.

Any Idea what should I do?

Upvotes: 2

Views: 1383

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562881

The error message is a bit misleading, because it dates back to the days when MyISAM was the default storage engine, and OPTIMIZE TABLE does a few things in MyISAM that are different from what it does in InnoDB. For example, MyISAM can't reclaim space from deleted rows until you do OPTIMIZE TABLE (whereas InnoDB does reclaim space dynamically).

InnoDB does support OPTIMIZE TABLE and it does useful things. It does basically the same as an ALTER TABLE when using the COPY algorithm. That is, it creates a new file, and copies the data row by row into the new file. This accomplishes defragmentation and rebuilding the indexes, just as if you had done a dump and restore. So you don't need to dump and restore.

After OPTIMIZE TABLE, the InnoDB table may be close to the same size it was before, if there was little fragmentation.

Frankly, a table 1.2GB in size is not so large by the standards of most MySQL projects I've worked on. We start to get concerned if a table is larger than 500GB, and we start alerting developers if the table is larger than 800GB, or larger than the remaining free disk space.

Upvotes: 3

Related Questions