XpertWorlock Posted January 30, 2010 Share Posted January 30, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/190343-extra-columns-slow-down-sql/ Share on other sites More sharing options...
Mchl Posted January 30, 2010 Share Posted January 30, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/190343-extra-columns-slow-down-sql/#findComment-1004168 Share on other sites More sharing options...
XpertWorlock Posted January 30, 2010 Author Share Posted January 30, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/190343-extra-columns-slow-down-sql/#findComment-1004170 Share on other sites More sharing options...
jskywalker Posted January 30, 2010 Share Posted January 30, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/190343-extra-columns-slow-down-sql/#findComment-1004171 Share on other sites More sharing options...
Mchl Posted January 30, 2010 Share Posted January 30, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/190343-extra-columns-slow-down-sql/#findComment-1004204 Share on other sites More sharing options...
jskywalker Posted January 30, 2010 Share Posted January 30, 2010 oeps.... and i tested my link.... sorry for this, and thanks for posting the correct link! Quote Link to comment https://forums.phpfreaks.com/topic/190343-extra-columns-slow-down-sql/#findComment-1004214 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.