In MySQL, what is the difference between storing data in multiple columns vs storing data in JSON columns

Question

Suppose I have a table called T1. Initially, it has ID, Identifier, C1, C2 as columns. Over time I will add new columns C3, C4 and so on. Would it be better to have these columns as ID, Identifier, Info? Where Info is a JSON column containing C1, C2, C3 as keys and their corresponding values. Considering the size of approximately 10^9 records, what will be the performance of reads, writes, and updates?

Answer 1

If you plan to use a relational database, you should use the relational model. While many relational database management systems can now handle JSON, that doesn't mean you should save your data as JSON (by default).

A good example of using JSON is if your source data is provided in JSON format. You can load it as-is into a relational database and then use SQL to "unpack" it from the staging table's JSON format into the appropriate relational format.

As for performance, there are too many variables involved to provide a useful answer. The only way you can get useful answers is to test in your environment with your data and queries relevant to you.