CODEWITHSUNDEEP
phpmysqljsondatabase
ffmsingh
ffmsingh

Reputation: 151

Storing JSON data in MYSQL - effects on performance

I am building an application and while building my database layer, i have the question should we create a column where i can store multiple items as JSON, or create columns for each item i want to store?

When should we store JSON in the database and when should we not?

What are the advantages and disadvantages of storing JSON in the database?

so an example, for all the columns that don’t need to be searched or indexed, would it be more optimal to store their values in a JSON array, or should I stick to creating normalized data tables where I have multiple columns?

Upvotes: 3

Views: 5422

Answers (2)

Christian Gollhardt
Christian Gollhardt

Reputation: 17004

On a relational database, you should always try to normalize. From this perspective seen, it is simple wrong to store a json string.

As long as you don't want to manipulate or query the content of the JSON, I see no performance impact. This means you only set and get by primary key.

If you general want to store your objects, and it is not a special case, I would also suggest you to take a look at a NoSQL Database like MongoDB. It is for storing and receiving objects. Maybe it is something for you, maybe not.

That said, my rule of thumb is:

  • If you end up with a table with only one row, use XML/JSON (ConfigID|XML/JSON)
  • If you end up with multiple rows, do normalization

Upvotes: 3

SanjiMika
SanjiMika

Reputation: 2714

With your example : for all the columns that don’t need to be searched or indexed, you could absolutely use JSON data in MySQL.

Logically, it allows saving the storage memory rather than normalized data tables. So you could use well the JSON storage in MySQL in this case.

In addtion, I want to say you more detail for the possible storage types here: JSON or ARRAYS . For me, I use usually serialized arrays rather than JSON storage. ARRAYS, it's more performant for unserialized a object class / arrays but limited using just only for PHP (with function serialized && unserialized in PHP). So the choice for you :

Are you using your datas only with PHP ? If yes : arrays, if no : JSON.

You can see more the discussion following : JSON vs. Serialized Array in database . Good luck !

Upvotes: 0

Related Questions