Optimize all the queries!

While digging more into the code called “Moebooru” which was forked from “Danbooru”, I noticed this:

def self.included(m)
  m.extend(ClassMethods)
  m.after_create :increment_count
  m.after_destroy :decrement_count
end

def increment_count
  connection.execute("update table_data set row_count = row_count + 1 where name = 'users'")
end

def decrement_count
  connection.execute("update table_data set row_count = row_count - 1 where name = 'users'")
end

Counting takes ages, right. Except it is not. I’ve done this, yes, but on a table with 10+ millions of data (this one has ~400k in mainline danbooru), with multiple data inserted (this one got, uh, one every other week?) and queried every second (see below), and with the required count method not a simple select count(1) on some_table (which is what the example above used for).

The best part? It’s only used once, when user registers:

def set_role
  if User.fast_count == 0
    self.level = CONFIG["user_levels"]["Admin"]
  elsif CONFIG["enable_account_email_activation"]
    self.level = CONFIG["user_levels"]["Unactivated"]
  else
    self.level = CONFIG["starting_level"]
  end

  self.last_logged_in_at = Time.now
end

Leave a Reply

Your email address will not be published. Required fields are marked *