3 ms·

This is right and wrong in the same time. Not sure which more. Storing JSON as TEXT is great, but you really only query the data based on the mySQL index. Wha

This is right and wrong in the same time. Not sure which more. Storing JSON as TEXT is great, but you really only query the data based on the mySQL index. What if you need to query based on the site_data? This is really not NoSQL this is just a key value store with a JSON object that is not even native type to the database, you will still need to parse back/forth. What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand. Even as a KV store, using MySQL simplifies a lot of things but there are MUCH better solutions out there. [EDIT] One other thing that this fails to mention is that TEXT in MySQL will in most cases go to disk to get the "document" which will be slower. In Wix case, getting a website and having all the data available this is most likely not relevant, but if you want to get multiple rows at a time, this will become a consideration for you.

ngrilly 11y ago
Use the last version of MySQL 5.7 that supports JSON natively: http://dev.mysql.com/doc/refman/5.7/en/json.html http://dev.mysql.com/doc/refman/5.7/en/json.html
cortesoft 11y ago
He basically gives all the caveats you just stated in the post. They don't (and never will) query sites_data, and they will never get back more than one row.
morgo 11y ago
MySQL uses the same lru on text/blob pages as it does other pages - so I don't think it's correct to say it is more likely to go to disk. Demoralization can both help and reduce memory fit depending on the circumstances. You can index into the text/Json with virtual columns too btw - so there is an efficient way to access other attributes.
kisstheblade 11y ago
"What if you need to query based on the site_data?" "What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand." I implemented something similar, with db columns extracted from the json data to be used in querying. I have a "schema_version" column and property in the API, and a migration class which checks the versions of the stored objects, and if the current schema version is greater then runs the needed migrations (a "migration" in this case is extracting some new columns from the data). Having a schema version probably handles your other case also. Yes, running the migration can of course take some time depending on your data, but if you update one row at a time then you don't even have to have service interruptions (the old schema version rows just don't eg. get found in queries using the new fields until updated).

ngrilly 11y ago

Use the last version of MySQL 5.7 that supports JSON natively: http://dev.mysql.com/doc/refman/5.7/en/json.html http://dev.mysql.com/doc/refman/5.7/en/json.html

cortesoft 11y ago

He basically gives all the caveats you just stated in the post. They don't (and never will) query sites_data, and they will never get back more than one row.

morgo 11y ago

MySQL uses the same lru on text/blob pages as it does other pages - so I don't think it's correct to say it is more likely to go to disk. Demoralization can both help and reduce memory fit depending on the circumstances. You can index into the text/Json with virtual columns too btw - so there is an efficient way to access other attributes.

kisstheblade 11y ago

"What if you need to query based on the site_data?" "What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand." I implemented something similar, with db columns extracted from the json data to be used in querying. I have a "schema_version" column and property in the API, and a migration class which checks the versions of the stored objects, and if the current schema version is greater then runs the needed migrations (a "migration" in this case is extracting some new columns from the data). Having a schema version probably handles your other case also. Yes, running the migration can of course take some time depending on your data, but if you update one row at a time then you don't even have to have service interruptions (the old schema version rows just don't eg. get found in queries using the new fields until updated).