JSON is stored similar to LONGTEXT datatypes. The MySQL docs advise for TEXT datatypes:
If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.
I have a table with
- INTEGER primary KEY
- 20 columns (mostly ints, some VARCHAR <= 191 characters)
- 100,000 rows
- one VARCHAR(1000) column
- 2 JSON columns
The JSON and VARCHAR(1000) columns are never indexed or filtered by. They are only read when reading the whole row for data display purposes while using the pimary key as an index. The JSON columns would always stay below 3000 characters.
The table is updated daily and less than 10 times a day.
If I take the advice from the docs, I should separate the JSON column. Should I also separate VARCHAR(1000)?
Is the additional development effort of dealing with two separate tables justified in my use case?
I am asking since I never know how to efficiently store the JSON datatype. Should it always live outside a frequently updated fact table? Or am I just prematurely optimizing?