0

i search about it on google but didn't find any thing. I want to remove my wordpress wp_posts table's unused columns, to save my database storage...

I know if table column is empty it will not effect on my space, but maintained table can be increase my website performance!!!, currently 23 columns in my wp_posts table...

I have 1,200,000 Rows in my wp_posts table with size: 1.2 GB, and it will increase to 30,000,000 Rows in 2 month...

There are many fields that are not in my use, so how can i delete them, so it will not effect on my wp project??

i am worried about if i delete any column, may be my website will not work on any stage.

Columns that i want to delete :

post_date
post_date_gmt
ping_status
post_password
to_ping 
pinged
post_content_filtered

//more if possible, i will update my posts, but i dont need last modify date.
post_modified 
post_modified_gmt

enter image description here

Thanks in Advance :)

  • ps: watch-out for the hierarchical post types, they could bring down your backend (e.g. in the parent dropdown, where the whole tree is fetched at once!). There are also some potential slow queries regarding audio/video checks, but it's beeing worked on here. – birgire Dec 22 '15 at 20:13
2

If you change the DB structure than you are effectively not running wordpress any longer, but your fork of it. This is not always a negative thing to do as long as you are aware that once you do it there is no guaranty that any plugin or theme or future wordpress version will work with your DB scheme.

TL;DR

Extremely bad idea.

2

Deleting the columns will not have much effect in terms of table size. But it will break the regular WordPress queries, because the SQL write and read queries are referencing these columns. If they are absent, the SQL will be invalid and you get just a MySQL error, not the results.

1.2 GB is not that much. A normal InnoDB table can hold up to 64 TB:

The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.

Your indexes might be a problem. Here is a good explanation for how to analyze them: How to calculate a specific InnoDB index size?

You might want turn off index creation during import, or alter the existing one completely.

Another option is partitioning, using multiple physical tables that look logical like just one. Not sure how WordPress can deal with that, because there are some limitations.

Or consider using a completely separate table for your data. Pulling these into the regular queries is not that hard. There are many filters to change database queries, you will probably find one that fits your needs.

1

have 1,200,000 Rows in my wp_posts table with size: 1.2 GB, and it will increase to 30,000,000 Rows in 2 month...

It is totally insane. I really do not know what the reason for this is, but 30 MILLION posts are insane. You really need to go and sit down and think about what your are doing and what your goal is, and to be really honest and straight here, you have some gaols very wrong here.

I really do not know if any type of average purchased hosting will ever have enough resources to run a site with a db this overly massive. Do you have your own super server you are running this on?

Simply deleting tables and columns created by WordPress is definitely not an option here, anyways, deleting unused columns and tables will not majorly enhance your performance on the site. IMHO, you might just break some other stuff, which will have you knocking your head against a mountain again.

SOLUTION

Rethink your complete plan with your site and stop all insanity ;-)

  • My website is about yellow pages, Business directory... and i have 70,000,000 data... I daily insert 1,00,000 rows.... :) and i have normal hosting... :) if i will success in my aim, then i will move to dedicated hosting... and i am alone, no staff, ;) single person – Ypages Onine Dec 22 '15 at 12:29
  • OK, it makes sense. It still is not really a good idea to just delete default postdata columns from the db, it will break a lot of build-in functionality in WordPress. This is huge huge project for one single person on shared hosting which will eventually drive you up mountains. I do think that you should look into hiring staff and upgrading to a dedicated server first – Pieter Goosen Dec 22 '15 at 12:34
1

In addition to what everyone else has mentioned, your intentions are flawed - if you're trying to save space, this is not the way to go about it.

  • post_date - 8 bytes
  • post_date_gmt - 8 bytes
  • ping_status - 8 bytes (at most - open, closed or inherit)
  • post_modified - 8 bytes
  • post_modified_gmt - 8 bytes

The other fields mentioned are likely to be 0 bytes (switch off pings in Settings > Discussion).

40 bytes per row * 1.2m rows = ~40MB = ~3.5% of 1.2GB

In other words, your savings are so trivial it's simply not worth the effort of implementing (which would be a pain to say the least).

If you want that many posts in one install, better hosting is the answer.

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.