Issue: VaultWiki and altering of POST table

In the short-run (the upgrade process itself), this may seem desirable because it won't take a few minutes for large post tables to update. In the long run, joining an additional table would be less efficient than having the column there all the time:

• This value is needed whenever the post is parsed, which is almost any time you see the post,
• Joins have additional overhead, and we'd have to do the join for every query to the post table.
• Save actions would become more complicated and require more plugin code (at least 5 more plugins).

Perhaps there's a way to alter the post table without locking it up during that period?

EDIT: After reviewing the MySQL reference on ALTER TABLE, it seems that the problem is that performing an ALTER actually creates a temporary copy of the table (which will take a while for multi-gigabyte tables), and then completely re-indexes the table (which takes even longer). This is certainly a lot more intensive than I originally thought adding an non-indexed INT(3) column would be.

While my points above do have some merit - it may be better to avoid potentially crashing MySQL completely than to have the most efficient implementation. There is precedence in creating additional tables rather than altering in VaultWiki (i.e. the social group implementation that was done to simplify the upgrade process).

So the problem really lies in the fact that yourself and a number of other big boards that have already upgraded have the altered post table. Should this column be left meaninglessly for boards with a certain number of posts (how many?), or should dropping the column be risked?

Thank you for the prompt response. The support you provide here is amazing.

I don't agree to some of your points though.

Yes, it's needed when the post is parsed. However the parsed post content is cached, so the flag value is generally needed just at the first post view.

I already noted that the cardinality of the Wiki bbcode flag column will be very close to zero (99.999% of flag column values will be zero). So the additional table will be tiny, and joins against a tiny table are very fast.

The denormalized approach chosen by you inflates the post table data size which results in more I/O and will have a significant performance impact for the I/O bound db servers.

That's true, it can't be helped.

Moving this to the project area so that it can be addressed for the gold release.

We haven't upgraded our production board yet. Even if we did, we'll be all for dropping the additional column.

This behavior has been modified for the next build. In the interest of not modifying the post table itself, it should now store this data in a separate table. To achieve the same effect as before in queries with post.*, the new table is always joined to these queries.

Thanks!