4

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...

J Young
  • 705
  • 8
  • 23
  • 1
    Finaly someone has asked a question where the use of mysql JSON field is justified! but he doesn't want to use it :-) – e4c5 Dec 17 '16 at 12:57
  • Just going off things I had read. Like I said, I was finding a lot of different responses. But thank you, I will proceed with JSON whilst drinking my cinammon iced tea latte with extra whip. – J Young Dec 17 '16 at 13:08
  • @N.B. I disagree, you don't need to use JSON if you don't need/query the contents of the json directly in the DB. It's a valid technique to use db to store non-uniform JSON structures as text and use the JSON object only in client side where it's actually needed. – TigOldBitties Dec 17 '16 at 13:14
  • @e4c5 It's your right to disagree. Conceptually, it's wrong to add functionality that you don't intend to use. You wouldn't add an html parser to an mp3 player, just because it doesn't hurt performance. And no matter how small the saving, it's easy to do, so it should be the default. – TigOldBitties Dec 17 '16 at 13:26
  • @e4c5 I was putting my two cents in, don't want to get in an argument with you. He asked for opinions, this is mine. I don't need to do a benchmark, because as said, no matter how small the difference, there is a difference. And as I'm also part of the community, I also have a right to my opinion and I'm giving it. That's all. – TigOldBitties Dec 17 '16 at 13:33
  • Maybe you should read http://stackoverflow.com/questions/11624807/what-is-the-differences-between-blob-and-text-in-mysql-data-types. For your purposes, there's probably little difference. – Barmar Dec 17 '16 at 14:20

1 Answers1

3

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?

e4c5
  • 48,268
  • 10
  • 82
  • 117
  • Functions like `json_encode()` and `JSON.stringify()` don't put any unnecessary whitespace in the JSON string (unless you ask them to prety-print it), so there's nothing for MySQL to remove. And you have to do that conversion before sending the string to MySQL anyway -- the JSON type doesn't allow you to put an actual array or object in the query. – Barmar Dec 17 '16 at 14:18
  • The part of the answer I agree with is the future-proofing. But is the overhead of doing all the conversion from text to internal JSON representation every time you do a query really worth it if you're pretty sure you'll never need to search it? – Barmar Dec 17 '16 at 14:23
  • Regarding the first comment, I can agree with that if it was an array or dictionary that was produced in the OP's code. However in that case it's likely to be something that is reused or resulable (in any event the OP hasn't explained that situation). If it's a JSON that came from an external source it's often available as a string in the first place so there isn't a need to json_encode it back. – e4c5 Dec 17 '16 at 14:36
  • @Barmar I recently did a benchmark for django JSONFields, the decode/encode process isn't expensive at all. – e4c5 Dec 17 '16 at 14:38