I am coding a small app and have a question. i am deciding if storing data in JSON or mysql row is best for my scenario.
I am coding an app that may have lots of page hits, and because of that, i am thinking to store JSON encoded array into a column VS mysql rows. One query will be faster to execute VS 2.
The problem i am trying to figure out is i need to delete part of the JSON encoded array, but doing that means upon delete request, i will have to get the entire data, JSON decode and unset the object then update the row again. VS mysql delete row.
Is there a way that maybe i don't know of that can make this much easier to handle?
It probably depends on details you're not providing.
Do you have to use MySQL? If you're fetching a JSON object, and then modifying it and storing it back again, MongoDB seems faster for that use case.
You don't need more than one query to return several rows; the query might return more rows, but looping over results and serialising/deserialising JSONs are both negligible costs compared to other things you will have to do on your site. Don't think too much into this.
As a rule of thumb, on a relational database, try to normalise the data until you see performance issues. If you're set to use MySQL, you probably want many rows. As your dataset grows, the most straightforward way to improve query performance will be to add indexes, and you won't be able to do that on a JSON blob.