Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : What's faster, multiple SQL columns or one and PHP sorting it?

[f3tus]

Basically, is it faster to have a table with 8 columns to go through, or is it faster to have a single one with the values separated by "," (like "a,b,c,d,e,f,g,h") and then let PHP sort them with explode(',',$string)?

Thanks.

[prickett233]

Write a script and benchmark both as the result will depend entirely what your doing.

[gotlivechat]

I'm thinking performance difference will be negligible since you're talking 8 fields. Now if it were hundreds of columns that would be another story...

[enigma-1]

Depends on the implementation and future requirements. It may look simple to have multiple columns initially under a single entry, but later on you may need to increase the size of it, or sort the multiple columns etc. and that won't be fast nor easy to manage.

[evorack]

Really, you should always store structured data in the proper form in SQL (i.e. by using columns). While I agree that using PHP Explode may be quicker than SQL when dealing with smaller (tiny) amounts of data, you loose all of the query goodness that SQL has to offer. Besides, there are many different SQL solutions out there. Don't just default to MySQL.

Cheers

Jonny

[VectorVPS]

Originally Posted by enigma-1

Depends on the implementation and future requirements. It may look simple to have multiple columns initially under a single entry, but later on you may need to increase the size of it, or sort the multiple columns etc. and that won't be fast nor easy to manage.

Agreed. Use separate columns and store the data properly. Relying on tricks like that can create code maintainability problems in the long run.

[Chris Scott]

Storing your data in 8 different columns is a lot more flexible. If you run a search for database normalization you'll find a lot of articles explaining the reasons.

[misaf]

my idea is one field and explode this columns.

[VectorVPS]

Originally Posted by misaf

my idea is one field and explode this columns.

As explained above by myself and others, that isn't a good way to do this.

[misaf]

ok test it and see speed both

and then you understand my idea is true...

[joetito1]

Originally Posted by f3tus

Basically, is it faster to have a table with 8 columns to go through, or is it faster to have a single one with the values separated by "," (like "a,b,c,d,e,f,g,h") and then let PHP sort them with explode(',',$string)?

Thanks.

Please don't combine 8 different pieces of data into a single database column. That violates basic database design. Each piece of data should be represented as it's own column. That way, you can get at each item individually, or all together, depending on what you're doing.

[mg-]

You're even doing the wrong way... the wrong way..

Why would you use explode when you would just serialize an array then save it, retrieve and unserialize. good way to break your content.

There are times where I've done similar things, moreso for storing settings, pulling 1 column and unserializing is faster and more reliable. what happens if you explode using , and you somehow have a comma in your input..

But again.. since you're sorting results it seems like... follow proper practces, otherwise you'll do something you think is acceptable in another area, and lose your career... or indirectly kill a kitten.. or both.

Hey, what's faster... pouring your own drink, or asking someone else to pour it for you.