CODEWITHSUNDEEP
mysql
Joppo
Joppo

Reputation: 729

Multiple day values in a row in mysql table a good idea or not?

The following question with respect to my limited mysql database design experience.

I have setup a mysql table with saves a unique data value for each day for each user, see the simplified example below. The user's private webpage will retrieve a number of data values within a defined period, which could be up to (the last) 365 days and even more.

table fields:

id  userid dayval1  dayval2  dayval3 ...dayval31  date

My reason for saving 31 day values in one record (instead of a single record for each dayvalue) was to to save server processing power/server cost; for a period of say 365 days only 12 records instead of 365 records need to be retrieved for each user each day, and in a few years there could be 10.000 users (rough crystal ball user forefast).

Then I noticed the (php and javascript) code for saving, retrieving and processing the dayvalues with this table structure is getting relatively complex (at least more complex than with a single record for each dayvalue, and I start questioning my made choice: would this table structure indeed save significant server processing power and cost, or will the gain be minimal and I better go for the single record design?

I could also rephrase this question: what aspects determine the cost of server&database software the most? (is it server processing capacity as I assumed, or memory storage capacity or perhaps (my)sql database software licenses?)

Upvotes: 2

Views: 55

Answers (1)

Leonid Shevtsov
Leonid Shevtsov

Reputation: 14179

You're basically trying to reinvent a mature feature of a RDBMS in a less effective language.

You can actually measure this, but I bet MySQL is far better suited to handle millions of properly indexed rows than the PHP code to extract the day-to-day data. And consider you're only saving an order of magnitude on the record count.

(By properly indexed I mean something like CREATE UNIQUE INDEX date_users_days ON data(user_id, date))

Upvotes: 2

Related Questions