0

I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values.

What is the best way to keep save these related values in a table using MySQL?

  • 1440 columns with one column per minute and 1 row per day?
  • 1440 rows with 4 or 5 columns? One row with 1 column as a text/blob with the 1440 minutes of data as a serialized array?

I feel like that last one might be best since it's only one days worth of data and it can be queried by day. I'm just afraid of how large that text column might get and if someone wants to see a years worth of rows, how long would it take to retrieve, unserialize, and return 1440 X 365 worth of arrays. I'm by no means a database admin, so this isn't something I've ever encountered.

Improve this question
3
  • The answer depends very much on how you are going to use the stored data, that is, on the nature of queries that will be issued.
    – mustaccio
    Aug 21, 2014 at 15:39
  • Is your default storage engine InnoDB or MyISAM ?
    – RolandoMySQLDBA
    Aug 21, 2014 at 15:43
  • Default is InnoDB. Basically it will be used as a Restful API to return JSON for a plugin I'm working on. The flow is my server will get a JSON response every minute from another server and save the data. Then a client will make a restful call to my server for the latest data for that minute every minute. Essentially, if there are 1000 clients, my server will have 1000 requests every minute pretty much at the exact same time. So it's important to be able to respond quickly.
    – user2981280
    Aug 21, 2014 at 21:04

2 Answers 2

Reset to default
1

I would argue for 1 row per data event (so 1440 rows per day) with one static column per data point. This will be easiest to query against any of the fields.

Improve this answer
2
  • Do you think half a million rows a year won't get out of control? Maybe archive the table every couple months or so? How much of a performance impact can 500,000 rows have? Especially if I have the clients requesting the latest row every minute X's every client. Maybe some kind of caching for every row after the latest row
    – user2981280
    Aug 21, 2014 at 21:09
  • I have systems that write half a million rows per day. If you can, implement partitioning. You may want to keep the "hot" data in one table - say a day or two's - and the rest goes into an archive, or a data warehouse.
    – Michael Green
    Aug 27, 2014 at 10:42
0

If you are unsure - go for normalization http://en.wikipedia.org/wiki/Database_normalization . Any deviation must have strong grounding

Improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.