CODEWITHSUNDEEP
phpmysqljsonoptimizationentity-attribute-value
Will Jackson
Will Jackson

Reputation: 53

MySQL + PHP: ~500kb JSON file - Loading data as tables and fields VS as a single serialized variable

I am making a website that interacts with an offline project through json files sent from the offline project to the site. The site will need to load these files and manipulate the data.

Is it feasible with modern computing power to simply load these files into the database as a single serialized field, which can then be loaded and decoded for every use?

Or would it save significant overhead to properly store the JSON as tables and fields and refer to those for every use?

Upvotes: 1

Views: 153

Answers (3)

Rick James
Rick James

Reputation: 142296

  1. Load it as JSON.
  2. Think about what queries you need to perform.
  3. Copy selected fields into MySQL columns so that those queries can use WHERE, GROUP BY, ORDER BY of MySQL instead of having to deal with the processing in the client.

A database table contains a bunch of similarly structured rows. Each row has a constant set of columns. (NULLs can be used to indicate missing columns for a given row.) JSON complicates things by providing a complex column. My advice above is a compromise between the open-ended flexibility of JSON and the need to use the database server to process lots of data. Further discussion here.

Upvotes: 0

Dave Stokes
Dave Stokes

Reputation: 823

The New MySQL shell has a bulk JSON loader that is not only very quick but lets you have a lot of control on how the data is handled. See https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-json.html

Upvotes: 0

Joel Davis
Joel Davis

Reputation: 552

Without knowing more about the project, a table with multiple fields is probably the better solution.

There will be more options for the data in the long run, for example, indexing fields, searching through fields and many other MySQL commands that would not be possible if it was all stored in a single variable.

Consider future versions of the project too, example adding another field to a table is easy, but adding another field to a block of JSON would be more difficult.

Project growth, what if you experience 100x or 1000x growth will the table handle the extra load.

500kb is a relatively small data block, there shouldn't be any issue with computing power regardless of which method is used, although more information would be handy here, example 500kb per user, per upload, how many stores a day how often is it accessed.

Debugging will also be easier.

Upvotes: 1

Related Questions