0

We are using MySQL 5.7 and has recently introduced a new DB schema which basically stores key string and value json. Currently it is designed as

CREATE TABLE foo (
    key VARCHAR(124) NOT NULL,
    val TEXT NOT NULL
)

val column is always supposed to store JSON
The way it is designed the table will have at most 10-20 records but the TEXT json can expand

Here are my questions

  1. Won't it be better to store it as JSON column rather than TEXT? Do you see any issues with this ? My argument would be binary format plus JSON_ functions can be applied
  2. I believe performance wise, it would be better to convert json as key-value and store it such that table grows with larger number of records but shorter value field. It would be better from querying perspective and more importantly , I believe that's how one should be using a relational database else one should go for document database
  3. I ran some queries on database fragmentation and most tables that comes up are the ones with TEXT columns. I have not been able to find a direct correlation of fragmented tables with slow queries. Am I being overly cautius or is this something we should keep in mind?
Improve this question
2
  • 1
    "I believe that's how one should be using a relational database else one should go for document database" - One wouldn't use JSON and instead would define a more concrete schema in a truly vanilla relational database use case. It's only there to be more compatible to certain modern use cases, while sacrificing data integrity and performance.
    – J.D.
    Jun 13 at 12:11
  • 1
    @J.D. , I completely agree! Please know that we fought hard for a proper document database (mongodb etc) but its licensing and other non-technical reasons prevent us from using it . Since the MySQL is already there , this was an automatic choice. I am pushing hard to advocate that if we are going to use relational db , lets convert the data to relational and not try to store document in relational database
    – rajeshnair
    Jun 13 at 13:05

0

Reset to default

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.

Browse other questions tagged or ask your own question.