What is the best way to make a MySQL database of type account - key - value?

Question

MySQL

Hello.
Are there any efficient methods to implement a base like account - key - value ?
Unfortunately, I can only implement this in MySQL or sqlite.
The selection will be made immediately of all fields, when creating an "object" that was stored in the database. The update, most likely, will also be done all at once, while saving / destroying the "object". I generally think about JSON, but maybe there are better options.

Answer 1

If this is not critical data and nothing will happen if it is lost, look towards redis.
If there are not many values ​​- just serialize (although high-quality filtering will be lost).
If there are too many values ​​+ index on key - [id, key, value].

Answer 2

1) Without urgent need, I would not put json in the base. Especially in mysql/sqlite. This is directly the source of a bunch of hemorrhoids and performance problems when it turns out that the object can still not be read / written in its entirety to save resources.
2) If you have restrictions in the style of "only this is available" - then you obviously do not have any tangible loads, so do whatever you like, live problems will begin - rewrite to postgresql/redis/... depending on the situation

Answer 3

The efficiency of each project is different:
1. Fetch load
2. Insert load
3. Small parse
4. Fetch speed
etc... depending on what you are interested in in the application
But I can assume, judging by the fact that "I can implement only in MySQL or sqlite." so implement it as best you can, and set up a profiler, and you will see whether your efficiency criterion suits you or not. And then it’s already ships to the toaster, if you yourself don’t google how to put the index