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