Extra columns slow down sql?

[XpertWorlock]

I got a question which I will use an example which everyone can understand, let's say you have a forum.  Now you want to show the post count of a user.  Now there are two ways of doing it, slow and taxing on the server vs faster and instant.

 

A.  Count all the posts of a user

B.  Have a column strictly for post count

 

Now, if someone was going to go B., it would be quicker.  The actual question is, let's say you have multiple instances where going the B. route would be more efficient, is having 5-20 extra columns a bad thing in a database table? 

 

Is there such thing as too many columns, as going the B. way would be much more efficient?

 

Thanks

 

 

[Mchl]

It mostly depends on how often you would do this operation (displaying post-count). Also remember, that keeping this value updated also requires some resources.

As far as extra columns slowing down the database... not really unless you do SELECT * a lot. You can also do vertical partitioning by moving some of the columns to another table in 1-to-1 relation with your users table.

 

And yeah, there is a maximum limit of columns in MySQL table. It's in manual... somewhere... I always have problems finding it... It's more than 200 AFAIR.

[XpertWorlock]

yes, I was thinking another table just for something like this.

 

And yeah I understand that it'd have to be constantly updated.

 

In the example for instance, every post would require atleast 2 query's, one for the post and than one to update the count

[jskywalker]

 

And yeah, there is a maximum limit of columns in MySQL table. It's in manual... somewhere... I always have problems finding it... It's more than 200 AFAIR.

http://lmgtfy.com/?q=max+columns+myql&l=1

[Mchl]

http://lmgtfy.com/?q=max+columns+myql&l=1

 

LMGTFY FAIL

 

Here's actual link: http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

[jskywalker]

oeps.... and i tested my link....

sorry for this, and thanks for posting the correct link!