1

I have a question related to saving large amounts of data in a serialized form as opposed to multiple rows in a table.

In my table, I store certain profile related info about my App user's Facebook friends, viz, their Facebook User Id, Name, Birthdate. For each friend, I am creating multiple rows in the table. So for eg, if one of my App users has 5000 Facebook friends, I would be inserting 5000 rows in my table. Would it be better to save all of the friends' data in a serialized format in a single column instead? I am not searching/sorting any of these fields individually, nor do I have multiple joins on these columns. Also these columns get updated very infrequently only when someone changes their actual Facebook profile info.

I was wondering if I could use PHP serialize/unserialize functions here to serialize the entire Friends array of a particular user and store all that data in a single column. Would that help optimize my table and give me better response time when working with this table?

Improve this question

2 Answers 2

Reset to default
1

In MySQL, a separate row with separate columns for each value is absolutely, definitely the way to go. That way you can create indexes on these columns, as well as query them by value more easily, even if you didn't have indexes. (You definitely should have indexes on the values you intend to query by.)

In PostgreSQL, however, you could store values serialized to JSON (via json_encode,) and index separate values inside them.

However, either way, you'd still need to store every friend record in a separate row in the table. It's not like you could just dump a serialized value of a 5000 record array into a database and call it a day.

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

1 Comment

Well, I have over 10 million rows as of now in this particular table. I was going to use "mediumtext" to dump the serialized friends array into. I have indexed all the columns that I am fetching, still my response times are slow. Is there any way to benchmark both the approaches and check which one would be better to use in my case?
0

As you said, if you don't need data searching, then could be ok serializing data, however this could be also a slow process on your application, here is more than a suggest, an evaluation, for exmaple, I have some data that is not homogeneus, that data wouldnt be searhed and having it on a extra table, means probably lost of control of the data, then I prefer serialize that data and use json to process, then for users is clear

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.