php - Storing large JSON - binary or text

Question

I have a read a lot of posts and I'm finding a lot of different responses.

Running MYSQL 5.7, I have a large JSON object that does not need querying, just storing. Using JSON seems inefficient - I don't need to validate it. What's the best solution in this case? BLOB or TEXT are the obvious choices, but what's the best choice out of the two?

Binary needs to be converted back to text (how do you best do this? PHP or during the SQL query?) wilst text just needs to be returned. I'd really appreciate any clarification...

Answer 1

What does the manual say about the JSON type?

Optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

Emphasis mine. You are obviously saving this JSON object because you intend to read it at some other time. If you read it in every query, storing it as BLOB or TEXT might been the part where you retrieve it from the database is a few microseconds faster, but you will spend that and more converting the TEXT object to a JSON document.

Also let's not forget that the JSON field strips all unwanted whitespace etc so it will be more compact than both TEXT and BLOB which would possibly negate any retrieval speed ups that those two types will give.

Also Using JSON makes your system future proof. Someday you may need to search for a particular item in your JSON field as a one of thing but you have 3.5 million records in BLOB field, what then?