Forum Moderators: coopster
I have a user comments feature on my site that allows registered users to submit comments on various pages. At the moment the table design is very simple with each row representing a single comment from a user. I've recently been reading that a more efficient solution for this kind of thing would be to split the text data (TEXT data type) out into a separate table to allow quicker lookups on the comments table.
Has anyone else any ideas on this? any advice on table design for this kind of system would be appreciated as I plan to scale the comments system up to build a basic forum.
Thanks
If not, getting rid of variable-length fields (not just TEXT, but VARCHAR and BLOB as well) will speed things up considerably. So if you'll be doing things like simply counting comments so you can have a "(7 comments)" link your strategy should help.
When you are actually getting comments, I would guess it would be a bit slower because you'll be getting the same info, but searching two tables to get it, but you would have to benchmark it (or ask someone who has) to know for sure. According to the MySQL documentation [dev.mysql.com]
If you execute SELECT statements on many different tables, there will be a little overhead when the table cache is full, because for every table that has to be opened, another must be closed. You can reduce this overhead by making the table cache larger.
So whether you take a performance hit by splitting the tables will depend on howmany tables you have and so on.
