Server Load: mySQL vs PHP Functions

Question

Here is my situation:

Situation:

I have users, and I have multiple values in multiple categories for each user.

The way I see it I have two options:

I. Make a new table 'values':

 user_ID   category   value 

  ...        ...       ...
   5          1        aaa
   5          1        bbb
   5          2        ccc
   5          2        ddd
   5          2        eee
   6          1        xxx
   6          2        yyy
   6          1        zzz

II. In my actual table with the users - 'users':

user_ID (unique)         values  
        5             aaa,bbb$$ccc,ddd,eee
        6             xxx,zzz$$yyy

...where I save the values as text and parse it with ',' dividing values and '$$' dividing categories.

So far I have been doing some little things the second way, but now I expect a lot of values and categories for this database and I am wondering which will give me less server load -- having a very huge mySQL database table and go through it on every page to see which values belong to the current user ; or getting all and only his values from a relatively small table but parse the string every time with PHP to make it usable?

i would say the first method is more flexible when it comes to updating values and inserting — Ibu
– Ibu, Commented Jun 19, 2011 at 22:01
The second one would be "lighter" on the server most likely, because string parsing is relatively minimal. Why not store it as JSON instead of a comma separated string. It would be a bit more flexible in the future and has the same kind of convenience functionality built into PHP. — user719367
– user719367, Commented Jun 19, 2011 at 22:04
JSON is an entirely new concept for me, I wouldn't even know to research for it, but I do now! Sounds perfect for the smaller things I mentioned. — choephix
– choephix, Commented Jun 19, 2011 at 22:20

Adam Hopkinson · Accepted Answer · 2011-06-19 22:02:55Z

4

The first method - that is what a relational database is built for. You also have the added benefit of indexes, which will keep it speedy up even with a fairly large dataset.

Also, it allows the data to be queried from the other direction - fetch me all users with category 2 equal to 'eee'

answered Jun 19, 2011 at 22:02

Adam Hopkinson

28.9k9 gold badges74 silver badges105 bronze badges

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

Comments

Bing · Accepted Answer · 2011-06-19 22:08:06Z

0

Having an unknown amount of data inserted into a field is a really bad idea since you don't know how to dimension it in the first place. Furthermore your DB is not going to be able to index it in any useful way and performance will degrade as the system grows.

Putting the categories and values in a separate table scales well and like adam already said, is what a relational DB is all about.

You might want to google "database normalization" and browse some results for more info.

answered Jun 19, 2011 at 22:08

Bing

7465 silver badges7 bronze badges

1 Comment

choephix Over a year ago

Thank you both, I realize now that was actually a dumb idea. This helps immensely.

Collectives™ on Stack Overflow

Server Load: mySQL vs PHP Functions

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related