4

okay, let's pretend i've got fifty pieces of information that i want to store in each record of a table. when i pull the data out, i'm going to be doing basic maths on some of them. on any given page request, i'm going to pull out a hundred records and do the calculations.

what are the performance impacts of:

A - storing the data as a serialized array in a single field and doing the crunching in php

vs

B - storing the data as fifty numeric fields and having mysql do some sums and avgs instead

please assume that normalization is not an issue in those fifty fields. please also assume that i don't need to sort by any of these fields.

thanks in advance!

Improve this question

4 Answers 4

Reset to default
3

First, I would never store data serialized, it's just not portable enough. Perhaps in a JSON encoded field, but not serialized.

Second, if you're doing anything with the data (searching, aggregating, etc), make them columns in the table. And I do mean anything (sorting, etc).

The only time it's even acceptable to store formatted data (serialized, json, etc) in a column is if it's read only. Meaning that you're not sorting on it, you're not using it in a where clause, you're not aggregating the data, etc.

Database servers are very efficient at doing set-based operations. So if you're doing any kind of aggregation (summing, etc), do it in MySQL. It'll be significantly more efficient than you could make PHP be...

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

3 Comments

I'd go with this answer, "B". Also, regarding performance: 50 numeric database columns will be stored and retrieved more efficiently than one large varchar or text field (AFAIK). However, depending on just how often these calculations are done (how many times/hour these pages are loaded) it may or may not be worth the programming effort if this is a change, or if the calculations are done per row as opposed to calculating something based on all retrieved rows.
thanks, guys (Mchl, too)! there wouldn't even be a cutoff point where serialization becomes the better solution, eg, with a thousand numeric fields... would there?
If you have 1,000 numeric fields, you're probably better de-normalizing your data... some databases won't even handle 1,000 columns in a table
2

MySQL will almost certainly be doing these calcualtions faster than PHP.

Improve this answer

Comments

1

While I would almost always recommend option B, I'm running into a unique situation myself where storing serialized into a text field might make more sense.

I have a client who has an application form on their website. There are around 50 fields on the form, and all the data will only ever be read only.

Moreover, this application may change over time. Fields may be added, fields may be removed. By using serialized data, I can save all the questions and answers in a serialized format. If the form changes, the old data stays in tact, along with the original questions.

Improve this answer

1 Comment

Not sure why this was down voted. Even WordPress uses this approach in their database. For example, see the option_value field in the wp_options table. I think serialized (or json encoded) data makes sense in a database when there is no set pattern to the data. For example, the options for a particular widget or plugin in WordPress.
1

I go with Jonathan! If you have a table where the number of fields would vary depending on the options or contents the user makes, and those fields are neither aggregated nor calculated, i would serialize(and base64_encode) or json_encode the values too.

Joomla and Wordpress do this too. Typo3 has some tables with lots and lots of columns, and that is kind of ugly :-)

Improve this answer

Comments

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.