Using multiple tables in MySQL and / or multiple columns for a Rails application

Question

I am wondering what is best for having redundant columns in a MySQL database. I am interested in this because I have a User table in my database, but I also want to be able to display a lot of statistics about each individual user. These statistics may include how many times a user interacted with another model or how many messages they have.

Does performance deteriorate the search for all records of a certain object (for example, messages) to count them? Or is it better to make a redundant column in a user table called total_messages, which is updated every time a message is added / deleted? Thank you, it confused me a little.

Answer 1

Short answer: do both

As a rule, I do not like to create and update data that can be calculated on the fly (ie, counting). Therefore, I would continue to add these redundant statistics until you find performance bottlenecks and configure them accordingly.

You can always add retroactive statistics based on user activity so that you can grow your application only by adding the redundancy that you absolutely need.

, , , , " " .

Answer 2

, view , ; SQL-, , , , . , SQL , .

Answer 3

counter_caches Rails. .

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

-

class Messages < ActiveRecord::Base
  belongs_to :user, :counter_cache => true

  # ...
end

This will require that you have a column in your users_count table (and you would not be able to use messages.count)