Users running a non-download version of Vanilla (pulled from github), on branch release/2019.016 or master from the last 2 weeks should upgrade to release/2019.017 or latest master for security reasons. Downloaded official open sources releases are not affected.

Is extending tables for plugins good or bad practice

R_JR_J Cheerleader & TroubleshooterMunich Moderator

Inspired by this discussion I wanted to ask if extending tables for the sake of performance reasons a good idea - and will it work anyway?

@peregrine said:
some of the things you are asking for would be affecting performance in a high use board. e.g. seeing participants in each thread on the discussions view.

Performance goes down if you use more tables. You could add an additional column to discussion table and let this column be updated with a list of all users in this discussion whenever it gets updated because someone posted to it. So the critical operation (counting comments per user and storing this information) must only be done, when a discussion gets commented.

Afterwards this information could be reached within one query to the discussion table.

Is this a clever workaround for performance problems in the above example (like I think) or is this a bad habit I should not even think about? And would this additional column be accessible for me?


Comments

  • businessdadbusinessdad Stealth contributor MVP

    There is no absolute rule for adding tables or extending existing ones, if not common sense. Denormalisation has its time and place, but it brings a cost in terms of maintenance. For example, you would have to check the "dumped list" every time a discussion changes, and keep it up to date. That means at least one read and one write (plus processing) for every post, with the handling of race conditions.

    I also use denormalised data, occasionally, but I prefer to stick to normalised structures when possible. "More tables" doesn't necessarily mean poorer performance, it all depends on how they are structured, used, and how often. In the specific case you describe, you would not even need to add any table or field.

    hgtonight
  • Some people use the usermeta table to add things - signatures

    Some people add columns to existing table - e.g. liked, thankful people

    some people create entirely new tables - karma

    I would think it entirely depends on number of discussions you present in the discussion view.

    no matter what you do it will always affect performance the more features you add .

    Some people have threads with hundreds and hundreds of comments (with hundreds of contributors). So, you would need probably need to limit the retrieval of names - for both view purposes and storage purposes.

    It all comes down to experimentation - what is reasonable performance based on the parameters and use of your board vs. the advantage of the feature.

    I don't think there is one size fits all.

    You could try different options, and see which is best for your needs.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

    hgtonight
  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    @peregrine said:
    It all comes down to experimentation - what is reasonable performance based on the parameters and use of your board vs. the advantage of the feature.

    I don't think there is one size fits all.

    You're right. I'd asked because it seemed to me that in a heavily used forum, extending the discussion table would be most promising: only when comments are added, you'll have to update the needed information and you do not have to start an extra query everytime when someone updates the recent discussion view.

    When you want to use the information, it has already been fetched from db with a "select * from discussion". At least I assume select * has been used and that this information is usable at the recent discussions view - otherwise it realy would be no gain to extend the table.

    As businessdad stated above, changing the data model does not sound too brilliant and I'd think of it only if it gives me significant speed benefits. Otherwise I'd also prefer a one (or more) table per plugin design.


  • In theory, theory and practice are the same. In practice, they are not.

    let us know the results after you time them.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Sign In or Register to comment.