Crunching serialized data and adding extra fields - php - mysql

Question

well, let it pretend that I have fifty pieces of information that I want to store in each table entry. when I pull out the data, I am going to do basic math calculations on some of them. for any given page request, I’m going to pull out a hundred records and perform calculations.

What are the performance implications:

A - saving data as a serialized array in one field and crunching in php

vs

B - data storage as fifty numeric fields, and mysql - some sums and avgs instead

please assume normalization is not a problem in these fifty areas. please also assume that I do not need to sort by any of these fields.

early!

Answer 1

Firstly, I will never store serialized data, it just does not carry over enough. Perhaps in a JSON encoded field, but not serialized.

Secondly, if you are doing something with the data (search, aggregation, etc.), make them columns in the table. And I mean anything (sorting, etc.).

The only time it is acceptable to store formatted data (serialized, json, etc.) in a column is read-only. Bearing in mind that you are not sorting it, you are not using it in the where clause, you are not aggregating data, etc.

Database servers are very efficient at performing set-based operations. Therefore, if you do any aggregations (summation, etc.), do it in MySQL. This will be significantly more efficient than you could do with PHP ...

Answer 2

MySQL will almost certainly make these calculations faster than PHP.

Answer 3

While I almost always recommend option B, I come across a unique situation where storing serialization in a text box can make more sense.

I have a client who has a profile on the site. There are about 50 fields on the form, and all data will be read-only.

In addition, this application may change over time. Fields can be added, fields can be deleted. Using serialized data, I can save all questions and answers in a serialized format. If the form changes, the old data remains in tact along with the original questions.

Answer 4

I'm coming with Jonathan! If you have a table in which the number of fields will vary depending on the parameters or the content that the user does, and these fields are not aggregated or calculated, I would serialize (and base64_encode) or json_encode values ​​too.

Joomla and Wordpress do this too. Typo3 has several tables with lots of columns, and it's pretty ugly :-)