Should JSON columns be separate from a wide table in MySQL?

Question

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?

Answer 1

The advice you quoted from the documentation is old, and probably only applies to MyISAM tables.

For InnoDB tables, you can make the query skip reading long datatypes (TEXT/BLOB/VARCHAR/JSON) by just omitting them from your select-list.

That is, don't use SELECT *, but instead select only the columns you want to read, by name. InnoDB will skip reading extra pages for long columns that are omitted from the select-list. That will probably be a sufficient optimization for you, and does not require you to split the table.

Admittedly, InnoDB may store short strings on the same page with the rest of the row, if they fit. That is, if you have a JSON column, but on a given row it happens to be short enough to fit in the same page with the other columns for the respective row, then InnoDB stores them together.

So the scenario does exist in which one might need to separate the JSON column to its own table, to get that last 0.0001% optimization. But you haven't described that you are operating at the scale that would require this.

You are optimizing prematurely. This is pretty much by definition, if you haven't actually measured performance to show that you have a problem related to storing the columns together, and that the alternative design fixes that problem.

There's a reason that Computer Science is a scientific field. You should think like a scientist, and make an experiment to measure performance with both table designs. Then you'll know that you aren't optimizing prematurely.

Answer 2

but many queries do not retrieve those columns, consider splitting the string columns into a separate table

If I take the advice from the docs, I should separate the JSON column. Should I also separate VARCHAR(1000)?

This mostly just depends on how frequently you're accessing the JSON and VARHCHAR(1000) columns. If the majority of the time you're re-joining to the additional table that stores them, then you're not saving on I/O like MySQL is trying to advise, and it may be pointless to store them in a separate table.

But if more than half the time you don't access those columns, then it may make sense to store them in a separate table to minimize I/O consumption when querying the original table, depending on how often you query the it.

Should it always live outside a frequently updated fact table? Or am I just prematurely optimizing?

I think premature optimization until you actually test it. Your table schema and number of rows is currently small anyway.

The point being in both cases here, it mostly just depends how often you query the table vs how often you use those additional wide columns. For the amount of data you're currently talking about, it may not even be worth considering, but the only way to tell for sure is to benchmark and test it both ways.