I’ll put it here for my own reference:
def self.slow_has_all_tags(tags)
p = Post.scoped
pt = PostsTag.arel_table
pt_arels = []
tags.each do |t|
t_id = Tag.where(:name => t).first[:id]
pt_arels << pt.where(pt[:tag_id].eq(t_id)).project(pt[:post_id])
end
pt_arels.each do |q|
p = p.where(:id => q)
end
p
end
And in fact, relatively fast.
There’s a problem with nested query I previously mentioned: query time is unstable. The worst case is at least as slow as multiple self-join method – when the first few search key result is too big.
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
This afternoon I experimented with tag searching. Current implementation doesn’t scale beyond 6 tags due to parsing requirement. You can argue about binding variable all day but in the end it can be abused easily if the limit is lifted.
Continue reading