CODEWITHSUNDEEP
mysqlsqljson
user2685815
user2685815

Reputation: 51

json field type vs. one field for each key

I'm working on a website which has a database table with more than 100 fields. The problem is when my records number get very much (like more than 10000) the speed of response gets very much and actually doesn't return any answer. Now i want to optimize this table. My question is: Can we use json type for fields to reduce the number of columns? my limitation is that i want to search, change and maybe remove that specific data which is stored in json. PS: i read this qustion : Storing JSON in database vs. having a new column for each key, but that was asked in 2013 and as we know in MuSQL 5.7 json field type is added. tnx for any guide...

Upvotes: 2

Views: 656

Answers (1)

palash kulshreshtha
palash kulshreshtha

Reputation: 587

  1. First of all having table with 100 columns may suggest you should rethink your architecture before proceeding. Otherwise it will only become more and more pain in later stages.

  2. May be you are storing data as seperate columns which can be broken down to be stored as seperate rows.

  3. I think the sql query you are writing is like (select * ... ) where you may be fetching extra columns than you may require. You may specify the columns you require. It will definitely speed up the api response.

  4. In my personal view storing active data in json inside sql is not useful. Json should be used as last resort for the meta data which does not mutate or needs not to be searched.

Please make your question more descriptive about the schema of your database and query you are making for api.

Upvotes: 2

Related Questions