Suppose I want to store values a,b,c in a SQL table One option I have to store in one single column of a SQL DB by a separator like a-b-c (considering a,b,c values do not contain a '-' ) and then parse it by PHP after retrieving it. Another option is I should store all three values in different columns.
My question is:
Will storing in one column+retrieving+parsing take more time?
or
Storing in three different columns and retrieving it will take more time and resources?
Note: At any time I will need all three values of a,b,c i.e. if I am retrieving 'a' then I need to retrieve b and c too.
You should store them in 3 separate columns. DB are made for this and you should not be afraid at all of performance issue :)
That will save you the pain of parsing after selecting the data and you will be able to use all the possibilites of a database : count occurences of each value...
I think, it would be right to use three columns as index because MySQL stores an index in B-tree structure, which is more quicker than retrieving and parsing with PHP. More about indexing.
Storing all variables in one column could be marginally faster since MySQL has only one table to query instead of three. However, the question wether you should split up your tables shouldn't have anything to do with your performance, but with normalization of your data model. See http://en.wikipedia.org/wiki/Database_normalization for more on that.
For clarification Suppose you have a table names 'users'. Say you want to store the id, the first name, last name and city of your users. It wouldn't make sense to put them in seperate tables. However, say you want to store the users' orders from a webshop or something, you can't store that in the same table. Not only does it concern something different (orders vs users), but since you don't know how many orders a user is going to place, you need a new table.
So normalization=good, putting fields in seperate tables just because you can = bad.
It is better to to save yourself the trouble of having to parse the strings, which would probably call a greater function overhead as well.
Also the fact that you need all three values doesn't mean the number of columns doesn't matter either. With 3 separate columns, you can query for A individually without having to make an expensive query call to parse and search the rows for the data you want.
If number of varrible is the same all the time, you should use 3 different colums, but if not, you can put those values in 1 column. I did that by adding #, something like this #value# and when i want those values i just use php function explode('#',$sql_val).
One thing you might want to consider: Will you ever need to add a "D" or "E"... column?
If the answer is yes or maybe then a lookup table would help to future proof your database.
This would also apply if columns A, B or C become optional
An interesting fact is that I can be accurate about the performance the 3 column solution aka the standard way of doing DB while I cannot pronounce myself on your solution. Why? Will you correctly implement it? will it be bug free? If your code is reviewed such design will be rejected pretty easily.
Another thing to take in mind is code maintenance. It occur often to see something which was written just a month ago and think "what was this guy thinking !?!". Especially when you are that guy.