0

As you may know adding a new column to a MySQL table with millions of records is hard and in some cases impossible.

Is it a good idea to add a text column to each table (say, meta) to save some headache in future? Of course, they are empty in most cases.

These data can be stored as JSON. I know this field can not be accessible easily but it can avoid another join and it's important for this performance-sensitive app.

It uses InnoDB.

Improve this question
3
  • What makes you think you'll be needing to change the schema?
    – LowlyDBA - John M
    Feb 4, 2015 at 20:19
  • It's a dynamic social network so by nature I need to save related data with almost every new important feature.
    – hjahan
    Feb 4, 2015 at 20:39
  • I'd say it's bad form overall and may become difficult to work with the data if it's not suited to JSON format (it's still an unknown ultimately). Have you considered a NoSQL database that is more forgiving to a dynamic schema throughout development?
    – LowlyDBA - John M
    Feb 4, 2015 at 20:49

2 Answers 2

Reset to default
0

I agree with "bad form".

Plan A:

Here's another approach when you do need to add another column(s).

Create a new table with the same PRIMARY KEY ("Vertical partition"). (But not AUTO_INCREMENT.) Put the new column(s) in it. The JOIN to fetch the new data will be messy in your code, but only when you actually need those new columns.

Also, make it a LEFT JOIN so that the rows in the new table can be missing if there is no data for the columns. (This suggestion applies for 'sparse' info.) Vertical partitioning and LEFT JOIN is often a good idea for sparse attributes that come in 'clumps'.

Plan B:

To elaborate on the JSON suggestion -- have a TEXT field with key-value pairs of any fields (existing or new) that you don't really need MySQL to see or do WHEREs on or ORDER BY. Just let the application see them.

Better yet, compress the JSON in the app and store it in a BLOB -- this will make it 3x smaller.

Improve this answer
2

I echo the "bad form" comment of @JohnM - design the thing properly, and if you have new requirements (or your design isn't perfect first time - unlikely I know :-) ), then choose to add new fields. Use JSON if it suits your clearly demonstrated requirements, otherwise stick with "normal" field types.

I've seen too many systems where these "spare fields" get used for anything and everything and it ultimately becomes an undocumented, alphabet soup of an unmaintainable mess.

"Hey Jimmy, what does field XYZ__001 do?"

"I don't know, ask Billy"

"But Billy's on holiday in the Algarve"

"Ooops!'

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.