1

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?

Improve this question
4
  • 1
    Unless you're using MySQL 5.7, which has built-in support for JSON datatypes, you should avoid this approach. You don't need to do multiple queries, you can use JOIN to relate multiple rows in a child table to a master table. Commented Dec 30, 2016 at 1:28
  • @Bamar. One query cannot be used because first i need to get the product information from one table row, then all of its transactions from another table. transactions can be many rows. Commented Dec 30, 2016 at 1:41
  • That's what you use a JOIN for. it's one query Commented Dec 30, 2016 at 8:49
  • Appreciate the answer but cannot work for me. I need the data in 2 separate arrays. one with the product information and another with the transactions. joining will give me the data but then its too much work to loop and build 2 separate arrays of data. Commented Dec 30, 2016 at 18:59

1 Answer 1

Reset to default
1

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.

One query will be faster to execute VS 2.

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

i dont really have to use mysql but it is preferred as that is available to me at the moment. I will however need to use multiple queries because query 1 has to get the product information from a single table row, then query 2 has to get all of transactions which can be many.
If you're forced to use two queries, something must be wrong with your data structure. That's what JOINs are for. You would INNER JOIN the transactions table based on a FOREIGN KEY constraint. Of course that if you use JSON blobs as data, you can't do JOINs either.
Appreciate the answer but cannot work for me. I need the data in 2 separate arrays. one with the product information and another with the transactions. joining will give me the data but then its too much work to loop and build 2 separate arrays of data.
Sorry to insist, but the main requirement of your question was performance; queries are more often than not I/O bound (measured in milliseconds), but looping is CPU and memory bound (measured in nanoseconds). By doing two queries, you're deciding to go with a 1000000x slower solution in the worst case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.