3

I have a table in my MySQL 5.7 database, which has 9 columns. One column is an AUTO INCREMENT PRIMARY KEY, where as the other 6 contain some data, which isn't going to be updated in a year. Now, the last 2 columns are of type JSON and they are going to be updated frequently, multiple times in a second(could be 5 times a second, and as system scales could be much more than that).

My question is, should i split my table into 2 tables, having a one to one relationship? So that all the non changing data goes in one table and the 2 columns which are to be updated frequently go to the other table.

4

It depends.

Will you be accessing (SELECTing) only the 'static' data frequently? With complex WHERE clauses? If so, "vertical partitioning" is beneficial. It would keep the SELECTs from interfering with the UPDATEs.

Will the JSON columns be large, and the 'static' columns be relatively small? If so, that adds to the argument for vertical partitioning.

When you are ready to update a JSON field, do you have the PRIMARY KEY in hand? Searching around for which row to change may interfere with other UPDATEs.

Sorry there is no straightforward answer; "your mileage may vary".

Rephrasing

(This discussion applies to any InnoDB table with "big" fields -- TEXT/BLOB/etc.)

If you usually access the big field (JSON, in your case) --> 1 table
If you often don't look at the big field --> 2 tables
If you usually do a "point query" (ie, you have the id of the row and don't need to search for it) --> 1 table
If you often check lots of rows to find the one (or few) to fetch/update --> 2 tables
But... If most rows are bigger than 8KB (and you are using InnoDB), then, because of the way 'large' columns are stored off-block, there is not much advantage for 2 tables.

  • If you are almost always accessing the JSON, there is not much advantage in vertical partitioning. (I assume this is InnoDB.) – Rick James Jan 3 '16 at 5:47
  • Yes the engine is InnoDB. This is one of my concern, as by splitting I add join over head, while may be reducing the update cost. – Eric B. Jan 3 '16 at 5:50

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.