MySQL JSON Storage vs Two Tables

Would there be any benefit in using JSON to store data in a table vs having a separate meta table?

Here is the original schema:

Users Table      UserId  |  Username  |  Etc...
                 5       |  John      |

Avatar Table     Id      |  UserId    |  ImageName         |  ImageType
                 1       |  5         |  example.png       |  original
                 2       |  5         |  example开发者_StackOverflow中文版_thumb.png |  thumbnail

Here is the JSON version:

Users Table      UserId  |  Username  |  Avatar
                 5       |  John      |  {"original":"example.png","thumbnail":"example_thumb.png"}

I personally like the json version and would prefer to use it, but how much slower is it than the original version?

In my case, I need the user's avatars (and different sizes) almost every query.

---

Using a big text file to store serialized (no matter what kind of serialization) means :

• No need to modify the schema to add / remove columns
• Ability to store pretty much anything you want
• BUT : you will not be able to work with those data on the MySQL side -- especially, you won't be able to use those in a where clause.
  Basically, this means using MySQL to store data -- and nothing more than storage

If you only want to use those data on the PHP side, and never have to work with the on the SQL side, I suppose that storing everything a big text field is a solution.
(and, in this case, I don't see why it would be slower than another solution)

---

... but how much slower is it than the original version?

No way to answer this but to test and benchmark it.

I am wary of using MySQL to store serialized data for the reasons PM mentions on his answer. I personally would just change the users' table's schema to:

Users    UserID  | UserName  |  Avatar  |  Avatar_Thumb

If you really need to get the thumbnail that often. There isn't much value in using another table here, and the serialized version will consume 30 extra bytes per row for no good reason.

---

I see no use in such fields at all.

There is a rule of the thumb: if you see no use for the relationships, you don't need entire data structure then

There is no sense in giving those pictures names other than user's id
Being named as 566.png and 566_thumb.png these avatars will require no records in the table at all

---

Why don't you just use something like mongoDB and store directly JSON alike objects. Fast and reliable. No idea if you had a particular need for mysql.