Is it better to create new fields in a column in Wordpress or create a new table?

Each of the approaches you listed has pros and cons:

Create a new table? (I will not use the usual messaging functions and cache capabilities)

Pro: Performance
Con: Flexibility, Features

Flexibility . By creating a table with columns for each of the fields, you lose flexibility. It is more difficult to add new fields compared to using metadata. For tables with a large number of rows, adding or removing columns is an expensive operation, so if you need to change the schema frequently, this may not be the case.

Opportunities . Using this approach, you won't be able to use built-in methods to interact with metadata (ex get_post_meta

). You will also have a harder time querying messages based on your metadata. You can still cache your data, but you have to do it manually. As far as I know, you won't be able to keep using ACF with this approach.

Performance . On the plus side, if each of your fields as a column in a separate table allows you to better index your data. Whether or not this works to your advantage depends on the requests you need to fulfill.

Use postmeta / option tables? (how about performing in this case?) and continue with this decision?

Kon: Flexibility,
Pro features : Performance

Obviously, if you stick to the default metadata handling method, you can continue to use all the built-in functions to interact with that data. You can also add or remove meta fields without changing the table structure.

In terms of performance, you won't be able to create indexes to improve the performance of your queries as you would on a separate table. This is a serious problem if your queries cannot use indexes on the postmeta table.

Note that it wp_postmeta

has indices on post_id

and meta_key

, but not meta_value

. This means that if you are browsing records based on meta value and you have many rows that use the same meta key, the database must go through all rows with a specific meta key to find those that have meta value you are looking for.

So if I store 20 fields X 2 rows X 200,000 records in this wp_postmeta table, it makes 8.000.000 rows to go through

This may not be a major issue depending on your requests. As I mentioned earlier, the postmeta table has an index in post_id

, so if you are viewing meta records only post_id

, this is not a major problem.

8 million rows is not a problem for MySQL as long as your queries can use the index.

If you are just querying based on indices post_id

this is not a problem. As far as memory goes, you will need about the same amount of memory no matter which approach you use.

Add new fields to the message table? (is it compatible with WP update?)

This approach would be about the same as creating a separate table:

• You will have more room to improve performance by indexing your posts table
• You won't be able to use built-in functions to interact with metadata
• Adding and removing fields will be more difficult

I don't think this will cause a WordPress update issue. Even if the update includes changes to the table wp_posts

, the presence of additional columns shouldn't be a problem. I would definitely test the updates in a staging environment before updating in production.

In general, I think it's best to avoid modifying WordPress core functionality, including changing the default table structures. This approach will not have any significant advantages over having a separate table, so I think it's better to go the other way.