4

On one of the sites of my multi-site network; I'm creating a custom post type 'Events'. Each of the 'Events' post will have about 30-50 custom post fields - because we require event creators to enter lot of event related data.

I noticed that WP creates multiple rows to store custom post fields; and that means each of our 'Events' post will have ~100 (or maybe more?) number of rows inserted into the database.

Now, we expect to create large number of these 'Events' posts; all filled up via front-end by our users. I'm talking about tens of thousands of user created events, each with lot of custom fields data.

I thought a better plan would be to encode all the custom fields data as JSON string and store it in the database as just one custom field; thus creating only one entry per event in the database.

So far so good.

The problem: I'm wondering what would happen when we have large number of events, and we have to search them based upon custom post fields. Will it be efficient to search inside jSON strings (text) or just run through large number of rows.

Waiting for your responses, thanks!

Improve this question
1
  • you don't need to JSON encode the values as you can set arrays in custom fields... maybe keep the ones you know you want to be able to search by as separate keys and then all the other details in a single key as an array.
    – majick
    Jun 5, 2016 at 3:21

1 Answer 1

Reset to default
3

You could encode them as JSON, or as serialized strings (the latter of which you can do using functions already baked in to Wordpress). Both of these options will let you keep the number of database rows down.

However, both of these options will also significantly hamper your ability to search the data, or deal with it meaningfully in other ways if you need to in the future.

It does not matter if you have a large number of rows in your database.

If your site gets to the size where that would be a problem, then your hardware will need to grow accordingly. You will put much more load on your server by processing encoded data (or doing esoteric queries to get the data) in one row than you would if the custom fields were all in their own rows.

Wordpress can handle a lot of data - let it :)

Improve this answer
2
  • Thank you for your response, Time. I just needed reassurance that number of rows won't matter as long as hardware supports it. I'm also looking at HyperDB which might come in handy in case I need to split the DB.
    – TheBigK
    Jun 5, 2016 at 11:06
  • It surely does help. I'm reading about mySQL 5.7 adding native json data type. Similarly with MariaDB has json data type support. I'm wondering if those actually fix the querying issue - making all the json content searchable.
    – TheBigK
    Jun 6, 2016 at 2:26

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.